DLookup

[NightPort]

Ok, I have a Record Series Name, and with each name there is a corrosponding Retention. Now what I would like to do is lookup the retention based on the name that was selected in the combo box. I am pretty sure DLookup does this, but when I do it i am always getting a #Error in my retention text box. Here is what my DLookup currently reads:

=DLookUp("[RetentionField]","tblRecordMgmt","[RecSerNameField] = " & Chr(34) & [RecSerName] & Chr(34))

 

[simongallop]

Couple of things: Why the Chr34?
Think that text variables need to be in single quotes ie [MyField]= ' " & me.txtBxInUse & " ' ....

HTH

 

[NightPort]

OK I've modified it a little because I didn't understand why I did that. So here is what I have now.

=DLookUp("[RetentionField]","tblRecordMgmt","tblRecordMgmt.[RecSerNameField] = Forms![frmAdditions]![RecSerName]")

It still gives me the #Error though.

 

[simongallop]

Try:

..."tblRecordMgmt.[RecSerNameField] = '" &Forms![frmAdditions]![RecSerName] & "'")

Note:: enclosed in single quotes ie = ' " & ...... & " ' " )
Have added spaces to make it easier to see.

HTH

 

[NightPort]

I still get the #Error. I can't seem to get it to work no matter what I try.

 

[simongallop]

Just seen where it is all located! I am presuming that you have a form with 1 combobox and 1 textbox. The user selects the name from the combobox and then the relevant information from RetentionField is shown in the textbox.

If this is the case then in the combobox, goto Properties and click on EVENT and choose On Change. A small box will show up and select CODE BUILDER

In here you need to have:

Me.txtboxname = Dlookup("etc..

Make sure that the text box is unbound

The only other thing that might throw you up is if the combobox has more than one column and the names aren't in the first column.

If the name is in column 3 then:

Me.txtBoxName = DLookUp("[Field]","

","[RecSerNameField] = ' " & me.cmbBoxName.Column(2) & " ' ")

Note that in cmbBox.Column the first column is called (0) and so on ie offset by 1

HTH

 

[NightPort]

Private Sub txtRetention_Change()
    
    Me.txtRetention = DLookup("[RetentionField]", "[tblRecordMgmt]", "[RecSerNameField] = ' " & Me.cmbRecSerName.Column(0) & " ' ")

End Sub

This is what I have in my code. It no longer gives me the error, but it doesn't do anything else either. This thing is giving me so many problems. I'm just stressed out from this hole project anymore. Thanks for the help you've been giving me, I do really appreciate it.

 

[Rich]

Why not just add Retention to another column in the combo box?

 

[NightPort]

because I need the retention to make a calculation for another bit of information. I am not sure if I could do that if it was a column in the combo box. If thats possible then I can do it that way.

 

[Rich]

Yes it is possible

 

[NightPort]

ok, that would work the best I think. I will work on that and get back to here when I need to work on this calculation if I can't figure it out. Thanks.

 

[Shep]

Skizzy, I've seen lookups 'work' but produce no results when the line has extra spaces in it.
Harry added spaces because the default font used in Internet Explorer makes it tough to read.

Anyway, you may find that the DLookup works fine when you remove the spaces. Try copying and pasting the line.

Me.txtRetention = DLookup("[RetentionField]", "tblRecordMgmt", "[RecSerNameField] = '" & Me.cmbRecSerName.Column(0) & "'")

'Course, you can always change the design a bit per Rich's suggestion.

Good luck.

P.S. I noticed that the font used in this board's Code format is proportional. You can see the single and double quotes much more easily.

 

[simongallop]

The code should be sitting in the OnChange event of the combobox NOT the textbox.

So it should start

Private Sub cmbRecSerName_Change()

By having it on the textbox it will only update if you type any old junk into the text box and tab out! Ny having it on the combobox the code will kick in when you have selected something from the combobox.

HTH

 

[Rich]

Of course if you insist on using one of the slowest methods of retrieving data then use the after update event, not the On Change

 

[NightPort]

Well I tried the different things. Moved the code into the On Change of the combo box and I recieve this error now.

Run-time error 2471
The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object "RetentionField."

My code is the same as above just in the combo box Change event. Thanks for all of the help.

 

[simongallop]

Me.txtRetention = DLookup("[RetentionField]", "tblRecordMgmt", "[RecSerNameField] = '" & Me.cmbRecSerName.Column(0) & "'")

It ought to work so long as the names are correct. Verify the following:

txtRetention is the name of the textbox that you are returning the value to.

[RetentionField] should be the name of the field that you want returned in the table that you are looking up.

"tblRecordMgmt" is the name of the table that you are looking at. It must contain [RetentionField] as a named field and [RecSerNameField] as a named field.

cmbRecSerName is the name of the combobox that you are using.

One potential problem. Do the field names in your tables end in the word "field"? ie In a contacts table do you create the fields as FirstNameField, SurnameField etc or do you follow the norm ie FirstName, Surname etc?

HTH

 

[NightPort]

I follow the norm. I will work with it in a little to see what I can, and I will check on everything you said to see if any of that works. Thanks for the patience

 

[simongallop]

As you follow the norm change [RetentionField] to [Retention] etc

 

[NightPort]

Alright everything is working on that. Now I just have to work on my calculation, but that is not difficult matter. At least I don't think it is. Thank you all for the help you provided.