Dlookup value based upon lookup selection

[David Wright]

Hello Folks

I am using Microsoft Access 2000

I would be grateful if someone could help me with “Dlookup”. I tried various methods of writing Dlookup and various events to trigger it, none of which were successful. I have been able to display the required value in a ‘text box’ which is a little helpful but not ideal.

I have a table called tblLogSheet. The ‘Description’ field of this table uses a lookup table called tblLkUpDescript ion. tblLkUpDescript ion contains two fields. The first field is called, ‘Equipment’ and the second field is called ‘Insulation’. I have a form called ‘frmNew_Applian ce’. This form is used to input new records into tblLogSheet. The data entry clerk selects an item of equipment from the drop down list of the combo box, derived from the ‘Equipment’ field of the lookup table. The selection is entered into the ‘Description’ field of tblLogSheet via the frmRecord_New_A ppliance input form. I would like the value listed in tblLkUpDescript ion, ‘Insulation’ field, which is adjacent to the selection made by the data entry clerk from the ‘Equipment’ field, to be entered automatically into my ‘Insulation_che ck’ field. The ‘Insulation_che ck’ field is on my frmRecord_New_A ppliance. This automated entry will update the tblLogSheet with the ‘Insulation_Che ck’ recording.

Please note - The clerk can write to the ‘Description’ field as appropriate selection may not be available in list. All fields are text fields. The number of descriptions listed in tblLkUpDescript ion table is currently 356. The ‘Description’ field name property is Combo128. The form is Single Form

tblLkUpDescript ion
The first field is: - ‘Equipment’ Field list – (This provides selections for ‘Description’ Field)
The Second field: - ‘Insulation’ Field (Contains the Value Required)

tblLogSheet
‘Description’ Field - (Records the Combo Selection - derived from ‘Equipment’ field list)
‘Insulation_Che ck’ field - (Needs Auto Entry Of adjacent value from Insulation Field)

Data entry for tblLogSheet is performed via ‘frmRecord_New_ Appliance’

Many thanks to anyone kind enough to reply

[puppydogbuddy]

David,

Place the following code behind frmRecord_New_A ppliance. Replace the illustrative object names used with their actual names in your application.... for example, replace YourCombobox with the actual name of yourCombobox.

Private Sub YourCombobox_AfterUpdate()
Me!YourCombobox.Requery
'selection from combobox used to update tblLogSheet
tblLogSheet.Equipment = Me!YourCombobox.Column(0)
tblLogSheet.Insulation = Me!YourCombobox.Column(1)
End Sub

[David Wright]

Thank you puppy dog buddy. I am affraid it did not work for me. I tried the following:

Attempt 1
Combo128 AfterUpdate

Private Sub Combo128_AfterU pdate()
Me!Combo128.Req uery
'selection from combobox used to update tblLogSheet
tblLogSheet.Des cription = Me!Combo128(0)
tblLogSheet.Ins ulation_Check = Me!Combo128(1)
End Sub

When I entered data into my form, the following was presented:
Run-time error ‘451’
Property let procedure not defined and property get procedure did not return an object.

When I clicked on Debug, the following line was highlighted
tblLogSheet.Des cription = Me!Combo128(0)

Attempt 2

Because I am selecting the description from the dropdown list which the ‘Descripti on’ field uses as a lookup; I tried removing one line of code. I hoped that it would then run and record the value from the second field of the lookup table (referred to as (1) ) into the Insulation_Chec k field. This did not work and the same error message was presented to me.

Attempt 3
I tried entering the code into the After Update event procedure of the form frmRecord_New_a ppliance

Private Sub Form_AfterUpdat e()
Private Sub Combo128_AfterU pdate()
Me!Combo128.Req uery
'selection from combobox used to update tblLogSheet
tblLogSheet.Des cription = Combo128(0)
tblLogSheet.Ins ulation_Check = Me!Combo128(1)
End Sub

I received a run time error 13 – Type mismatch

Then I tried this:

In Form Afterupdate

Private Sub Form_AfterUpdat e()
Me!Combo128.Req uery
'selection from combobox used to update tblLogSheet
tblLogSheet.Des cription = Combo128(0)
tblLogSheet.Ins ulation_Check = Me!Combo128(1)
End Sub

No error message came up but the required ‘automated ’ entry was not made into the Insulation_Chec k field.

Is there anything else you suggest? The tblLogsheet does have other fields one of which is a primary key field. Regards David

[puppydogbuddy]

David,
this is in response to your first attempt...you did not completely follow the syntax I gave you.

Change this:
tblLogSheet.Description = Me!Combo128(0)
tblLogSheet.Insulation_Check = Me!Combo128(1)

to this;

tblLogSheet.Description = Me![Combo128].Column(0)
tblLogSheet.Insulation_Check = Me![Combo128].Column(1)

Also, note that wherever you have spaces in an object name like Combo 128, you have to enclose the name in brackets [Combo 128].

After you make all of the above suggested changes, get back to me.

[David Wright]

Dlookup Value based upon lookup Selection

Hello PuppyDog

Thank you for writing again. I tried what you suggested and this is the result.

Private Sub Combo128_AfterU pdate()
Me!Combo128.Req uery
'selection from combobox used to update tblLogSheet
tblLogSheet.Des cription = Me![Combo128].Column(0)
tblLogSheet.Ins ulation_Check = Me![Combo128].Column(1)

End Sub

Run time error ‘424’
Object Required

[puppydogbuddy]

oops, I used the bang operator, where I should have used the dot operator. If you still get an error try to identify the error line.

Private Sub Combo128_AfterUpdate()
Me.Combo128.Requery
'selection from combobox used to update tblLogSheet
tblLogSheet.Description = Me.[Combo128].Column(0)
tblLogSheet.Insulation_Check = Me.[Combo128].Column(1)

End Sub

[David Wright]

Hello PuppyDogPal
I now have the answer thanks to your guidance. Please see below:

Private Sub Combo128_AfterU pdate()
Me.Combo128.Req uery
'selection from combobox used to update tblLogSheet
Forms!frmRECORD _NEW_APPLIANCE! Description = Me.[Combo128].Column(0)
Forms!frmRECORD _NEW_APPLIANCE! Insulation_Chec k = Me.[Combo128].Column(1)

End Sub

I could not have got there without your help. Many, many thanks David.