Using a textbox on a form with a table (1 Viewer)

[Rick Stanich]

(Solved) Using a textbox on a form with a table

Hi, Im quite new to Access and database design allthough I have practical VBA and VB experience, I notice I dont have enough to do minor techniques in Access.

I have a textbox on a userform that a user will type in a value and I would like to check if the value is in a table, if not prompt the user and return focus to the textbox.

Future case would be to find a "Like" condition and let the user select from any available choices from the table. But I will settle for simply checking against a table.

I have searched and still am, I don't know the correct language to format my search's; thus it takes me a while to find things.
Any hints, tips and or examples are appreciated.

 

[RuralGuy]

One method would be to use a DLookup() in the BeforeUpdate event of the control.

 

[HiTechCoach]

Have ypu tried using a combo box with limit to List" set to Yes and Auto Complete = yes. This will automatically handle everything you want (even the Like part ). No VBA code required.

TIP: Use the control wizard to create the combo box.

 

[Rick Stanich]

Thank you for the info.
If you would lend your time further.

I get an error with this string

Private Sub sPartNumber_BeforeUpdate(Cancel As Integer)
sPartNumber.Value = DLookup("txtPartNo", "tblParts", _
        "Criteria = '" & Forms!Form1!sPartNumber & "'")
End Sub

Run-time error '2471':
The expression you entered as a querry parameter produced this error:
'The object doesnt contain the Automation object "Criteria".'

I do not know what the error is telling me other than I believe the string "Forms!Form1!sPartNumber" is not correct.

Notes:
sPartNumber is a TextBox on a userform (Form1).
Form1 is a userform
tblParts is a table
txtPartNo is a key(?) from tblParts

 

[Rick Stanich]

Have ypu tried using a combo box with limit to List" set to Yes and Auto Complete = yes. This will automatically handle everything you want (even the Like part ). No VBA code required.

TIP: Use the control wizard to create the combo box.

Odd that you mention this because I am working on this as well, I assumed understanding the textbox would aid me in understanding a combo box.

Thanks

Edit:
Its not as pretty as my userform but it works quite well, again thanks for the tip!

 

[RuralGuy]

Try:

Private Sub sPartNumber_BeforeUpdate(Cancel As Integer)

If DCount(* , "tblParts", "txtPartNo = '" & Me.sPartNumber.Text & "'") Then
   Cancel = True
   MsgBox "Duplicate value"
End If

End Sub

...if it is a string (text) value, or...

Private Sub sPartNumber_BeforeUpdate(Cancel As Integer)

If DCount(* , "tblParts", "txtPartNo = " & Me.sPartNumber.Text ) Then
   Cancel = True
   MsgBox "Duplicate value"
End If

End Sub

...if numeric.

 

[Rick Stanich]

With a minor edit, your code works. Had to add quotes around the asteric.

If DCount([COLOR=red]"[/COLOR]*[COLOR=red]"[/COLOR], "tblParts", "txtPartNo = '" & Me.sPartNumber.Text & "'") Then
   Cancel = True
   MsgBox "Duplicate value"
End If

Thanks for your input!

 

[RuralGuy]

I knew that! I was just testing you. Glad you got it sorted.

 

[Rick Stanich]

Final code:

If DCount("*", "tblParts", "txtPartNo = '" & Me.sPartNumber.Text & "'") Then
    'MsgBox "Match found" 'for testing
Else
    Cancel = False
    MsgBox "The part number doesn't exist in this database. Please re-enter the part number."
    'do some code here
End If