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 tblLkUpDescription. tblLkUpDescription contains two fields. The first field is called, ‘Equipment’ and the second field is called ‘Insulation’. I have a form called ‘frmNew_Appliance’. 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_Appliance input form. I would like the value listed in tblLkUpDescription, ‘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_check’ field. The ‘Insulation_check’ field is on my frmRecord_New_Appliance. This automated entry will update the tblLogSheet with the ‘Insulation_Check’ 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 tblLkUpDescription table is currently 356. The ‘Description’ field name property is Combo128. The form is Single Form

tblLkUpDescription
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_Check’ 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_Appliance. 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_AfterUpdate()
Me!Combo128.Requery
'selection from combobox used to update tblLogSheet
tblLogSheet.Description = Me!Combo128(0)
tblLogSheet.Insulation_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.Description = Me!Combo128(0)

Attempt 2

Because I am selecting the description from the dropdown list which the ‘Description’ 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_Check 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_appliance

Private Sub Form_AfterUpdate()
Private Sub Combo128_AfterUpdate()
Me!Combo128.Requery
'selection from combobox used to update tblLogSheet
tblLogSheet.Description = Combo128(0)
tblLogSheet.Insulation_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_AfterUpdate()
Me!Combo128.Requery
'selection from combobox used to update tblLogSheet
tblLogSheet.Description = Combo128(0)
tblLogSheet.Insulation_Check = Me!Combo128(1)
End Sub

No error message came up but the required ‘automated’ entry was not made into the Insulation_Check 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]

Hello PuppyDog

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

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

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_AfterUpdate()
Me.Combo128.Requery
'selection from combobox used to update tblLogSheet
Forms!frmRECORD_NEW_APPLIANCE!Description = Me.[Combo128].Column(0)
Forms!frmRECORD_NEW_APPLIANCE!Insulation_Check = Me.[Combo128].Column(1)

End Sub

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