Dlookup referencing text box #Type!

[JamesN]

Hi,

In a form I have set up (Form1) I type a Case ID into a text box and from this 4 other text boxes need to be populated, this info is pulled from a table I have setup. Within the table there are Case ID, Investigation, Customer, Business and Account columns (all text columns apart from Case ID which is number)

I've tried using a dlookup formula to reference the value entered in the Case ID text box called txtcaseid, to look in the the table called tblwarehouse and output one of the results in the columns eg Investigation. The investigation result text box is called txtinv. Formula I have tried is below.

=dlookup("[Investigation]","tblwarehouse","[CaseID]='" & Txtinv & "'")

I am getting a #type! error.

I'm still quite new to Access. Any help would be appreciated.

Thanks in advance

James

 

[Ranman256]

usu in a table , ID is numeric. Is your CaseID field: numeric or string?
if numeric, remove the quotes:

=dlookup("[Investigation]","tblwarehouse","[CaseID]=" & Txtinv )

 

[JamesN]

Hi,

Thanks for the response.

The Case ID field is numeric. I removed the quotes as suggested below on the Case ID part of the formula but still receiving the same error ?

 

[JamesN]

Just to add - on the formula there is an error box showing saying 'invalid control property : control source'

 

[Minty]

If you made your case ID box a combobox based on your case ID table, you could pull in the other fields you need as extra columns and refer to them directly in the after update of the combobox.

Me.txtInvestigation = Me.cmbCaseID.Column(1)
Me.txtCustomer = Me.cmbCaseID.Column(2) ...etc

The columns in a combo box are numbered from (0) so (0) would be the Case ID if you pulled the fields in in the same order as your post above.

 

[JamesN]

Hi, that would do the trick. The only issue with that way is the large volume of case IDs which would be showing. How easy would it be to limit the no of case ID showing based on other columns eg user / date so I could select a user or date in another combo box etc to limit the no of case ID showing ?

 

[Minty]

You can build a query for your combo box record source based on as many criteria as you want to put in. So either/or/and - a user, date, customer etc. And you can use controls on your form in the underlying query. Use the builder in the criteria to assist you.

 

[JamesN]

Thanks for the response. I'm very new to queries. Am i correct in thinking i create a combo box looking at the user list table, then another combo box which is linked to a query with the columns Case ID and User, and in the query it would have as critieria for user "[Forms]![Form1]![UserComboboxreference]"?

 

[JamesN]

Based on how I would like the form to work I think the Dlookup is going to be best, could anyone direct me how to get around the #type! error?

Thanks

 

[JamesN]

Sorted. Managed to crack it.