Please i need some help it's killing me!

abrahom · June 26th, 2009, 09:59 AM

Hello everyone,
I'm trying to open an existing record on a form (frmgood) using a button (findrecord) on different form (frmrecord). The user enter a number on textbox (text1) on frmrecord, and if the number matches the existing record [IDA], frmgood opens on the right existing record. If the text1 doesnt match IDA, a msgbox should appear saying "Enter the right record number" and if the user hit the button (findrecord) without entering a number on text1 a msgBox should appear saying "Please enter a number".

I know its an if statement, however i couldn't make it work. The only thing that works is opening the existing record by using the line code below:

DoCmd.OpenForm "frmgood", acNormal, , "[IDA]=" & Me("text1")

Any help would be really appreciated. I'm a new user, and i'm using Access 2002. Thank you in advance

mmcdonal · June 26th, 2009, 12:11 PM

Here is what I usually do for input validation:

Code:

Dim sIDA As String 'I am assuming it is a string
Dim sLink As String
 
If IsNull(Me.IDA) Or Me.IDA = "" Then
   MsgBox "Please enter an IDA.", vbInformation
   Me.IDA.SetFocus
   Exit Sub
Else
   sIDA = Me.IDA
End If
 
sLink = "[IDA] = '" & sIDA & "'"
 
DoCmd.OpenForm "frmgood", acNormal, , sLink

If IDA is a number, then

Code:

Dim lIDA As Long
Dim sLink As String
 
If IsNull(Me.IDA) Or Me.IDA = 0 Then
   MsgBox "Please enter an IDA.", vbInformation
   Me.IDA.SetFocus
   Exit Sub
Else
   lIDA = Me.IDA
End If
 
sLink = "[IDA] = " & lIDA
 
DoCmd.OpenForm "frmgood", acNormal, , sLink

That will check to make sure a number was entered, and then stop the form from opening until they do select a number.

I would use the combo wizard to create a look up combo box on the IDA field, and then have them make a selection from the combo box. That way you know that whatever they choose is a valid selection, at least. Then you won't have to check for Nulls.

Code:

Dim lIDA As Long
Dim sLink As String
 
If Me.cboIDA = 0 Then
   MsgBox "Please enter an IDA.", vbInformation
   Me.cboIDA.SetFocus
   Me.cboIDA.DropDown
   Exit Sub
Else
   lIDA = Me.cboIDA
End If
 
sLink = "[IDA] = " & lIDA
 
DoCmd.OpenForm "frmgood", acNormal, , sLink

Did that help?

abrahom · June 26th, 2009, 12:57 PM

Private Sub Enter_Click()
Dim lIDA As Long
Dim sLink As String

If IsNull(Me.text1) Or Me.text1 = 0 Then
MsgBox "Please enter a record number.", vbInformation
Me.text1.SetFocus
Exit Sub
Else
lIDA = Me.text1
End If

sLink = "[IDA] = " & lIDA

DoCmd.OpenForm "frmgood", acNormal, , sLink ( here an error 2501 appears, saying "Openform action was canceled)

The if statement work perfectly if i enter 0 or i leave it blank.
I also want to add an if statement to be operated whenever the user enter a number (in text1) that doesnt exist as a record (IDA), or if it exists it will open frmgood at that record number (IDA). I really appreciate your time and help.

mmcdonal · June 26th, 2009, 03:24 PM

That is why I suggested using a bound combo box to the column the user is selecting.

For example, if you run the combo box wizard on the first form, and have it look up values in IDA, and limit it to the list, then your users will ONLY be able to select a valid number. So there is no need to check for the validity of the number.

If you have this situation:

ID IDA
1 1X23
2 2B47
3 9S43

etc, you can still use this method, but you have to point the variable at cboIDA.Column(1) instead.

Will that work for you?

abrahom · June 26th, 2009, 03:59 PM

It works nicely! Good guy. I complicated my life by having a textbox, i dont know what i was thinking. You have been so helpful to me, i really appreciate that.