DLookUp problem (new to Access)

[Zenobia]

New to Access, and ran into an issue when I tried to create a DLookUp for an Issue form I've made.

I am trying to get it so that when the RentalCode for a product is updated all of the other information on the form will be filled in accordingly (in this case, stock rental table information). My first attempt was to get the Title to appear when the rental code is typed in.

Here is the Code that I've tried:

Private Sub RentalCode_AfterUpdate()

Title = DLookup("Title", "Stock", "RentalCode=" & RentalCode)

End Sub

After setting this up, I had a run time error thrown back stating the following.

"Runtime error 2471

The expression you entered as a query parameter produced this error 'RE00014'"

To save confusion RE00014 is the identifier allocated to the DVD title in question.

When I ran the debug to find why the Rental Code was causing an issue I found that the Title was returning a Null value. The Debugger wasn't very helpful and I'm still left clueless as to what is going on. Any suggestions?

I've check the field names are all spelt correctly by the way.

 

[pbaldy]

The syntax is different for a text field:

http://www.mvps.org/access/general/gen0018.htm

 

[John Big Booty]

Welcome to the forum.

It would seem the criteria for your DLookup is a string, so should be enclosed in double quotes, try;

Private Sub RentalCode_AfterUpdate()

Title = DLookup("Title", "Stock", "RentalCode= " & [COLOR="Red"]CHR(34) &[/COLOR] RentalCode [COLOR="Red"]& CHR(34)[/COLOR])

End Sub

Additionally you might also want to consider a naming protocol for your DB objects and controls, something along the lines of; TBL_TableName, FRM_FormName, QRY_QueryName etc. Also avoid using spaces and other special characters in names, limit yourself to alpha and numeric characters and the underscore (_)

This would make it quite clear in this instance that Stock is a table rather than some other object.

 

[Zenobia]

Thank you both very