Trouble with DLookUp

[Sgt Bilkp]

Hi again. Having a bit of trouble with a DLookUp on my form (frmMain_data)

I have a combo box (cmbType) which pulls it's result from a table (tblRadioTypes).

tblRadioTypes has the *ID, Type, and Model as the fields. I want to be able to select a radio in my combo box and have a text box auto-populate with the Model (which is a numerical value) from the table.

I got this far in the text box (txtType) which gives me #Error.....

=DLookUp("Model","tblRadioTypes","Type = " & Forms!frmMain_data!cmbType)

 

[pbaldy]

Does this help?

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

If Type is text the value would have to be surrounded by single quotes.

 

[Mike375]

Don't know if this helps

=DLookUp("[Life4]","1LOIRatesFnew","[AgeNB]=[Forms]![SumPremAllCats1]![anb]")

Life4 is a field in the table 1LOIRatesFnew and AgeNB is a field in the table and it matches the value in field [anb] on the form SumPremAllCats1

[anb] on the form is a combo

I have them as SetValue actions in macros.

 

[Sgt Bilkp]

Neither of them appear to be working. The Model field is a numerical value, the Type is Alpha

 

[missinglinq]

I'm confused! If your combobox cmbType pulls from the tblRadioTypes table, why not simply include the [model] field in the combobox, then in its AfterUpdate event, assign the [model] value to the textbox, instead of user the relatively slow DLookup() function?

 

[boblarson]

Change your field name from TYPE as TYPE is a reserved key word in Access (and VBA, VB, VB.NET, C#, etc.).

Here's a good reference for those:
http://support.microsoft.com/kb/q286335/

 

[Sgt Bilkp]

I'm confused! If your combobox cmbType pulls from the tblRadioTypes table, why not simply include the [model] field in the combobox, then in its AfterUpdate event, assign the [model] value to the textbox, instead of user the relatively slow DLookup() function?

Interesting. Following Bob's advice the field Type is now Types anyway (which didn't fix it).

If i include the Model field in the combobox, can it be hidden from view (don't want to columns in the box)?

How would i do an AfterUpdate event for this?

 

[Zaeed]

What you would do is base the combo box on a query that has 2 columns.
Column 1 would be Types and column 2 would be Model
On the combo box properties, have column count as 1, bound to column 2.

Then, on the combo box click event, set the text box to the value of the combo box. Since it is bound to column 2 which is the model, the text box will become this value

 

[missinglinq]

Sure, goto Properties - Format and in Column Widths, set the second column with to zero. Then in the AfterUpdate for your combobox put:

Private Sub cmbType_AfterUpdate()
  Me.txtType = cmbType.Column(1)
End Sub

Note that Columns is zero based, so that the second column, that holds Model is indexed as 1.

 

[Sgt Bilkp]

Sure, goto Properties - Format and in Column Widths, set the second column with to zero. Then in the AfterUpdate for your combobox put:

Private Sub cmbType_AfterUpdate()
  Me.txtType = cmbType.Column(1)
End Sub

Note that Columns is zero based, so that the second column, that holds Model is indexed as 1.

That's got it! Excellent.

Now the only problem is, on opening the form the txtType is blank until the AfterUpdate event. How can this be displayed all the time, after i have selected a value in the cmbType combobox?

Should i have mentioned this first. I probably think so. Sorry.

 

[Zaeed]

What do you want it to display when the form opens?

 

[missinglinq]

If you mean that after making a selection from the combobox, txtType is empty when leave the record and then return to the record, or that it's empty when you close the form then reopen it, it means that txtType is not bound to a field in your underlying table/query! For data to persist or stay with a record, it has to be a part of the record!

Linq

 

[boblarson]

If you mean that after making a selection from the combobox, txtType is empty when leave the record and then return to the record, or that it's empty when you close the form then reopen it, it means that txtType is not bound to a field in your underlying table/query! For data to persist or stay with a record, it has to be a part of the record!

Linq

So, instead of storing that information, which is already in the combo box, just set the text box to update in the form's On Current event with the same code:

Me.txtType = cmbType.Column(1)

 

[Sgt Bilkp]

Forgot to bound it to a record in the main data table - cheers for the help!