Auto-populate form using Dlookup from multiple table fields

[Leo S.]

I have a form that I fill out that has 2 fields - PSN_Number &
Channel_Number. What I want to achieve is for another field to auto populate
with data from a 3rd field (Degraded_Serial_Number) which is dependant on
what is entered in the 2 fields above. I have a table that corresponds this
info - HDD_List. No matter what I have tried, I cannot get it to work.
Below is the code that I am using:

Private Sub Channel_Number_AfterUpdate()
On Error GoTo Err_Channel_Number_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "Channel_Number = " & Me!Channel_Number

' Look up HDD Serial Number and assign it to Degraded Serial Number
control.
Me!Degraded_Serial_Number = DLookup("HDD_Serial_Number", "HDD_List",
strFilter)

Exit_Channel_Number_AfterUpdate:
Exit Sub

Err_Channel_Number_AfterUpdate:
MsgBox Err.Description
Resume Exit_Channel_Number_AfterUpdate

End Sub

Adding the criteria to the DLookup line is where I am lost. Hopefully
someone can point me in the right direction.

TIA

 

[Alex Dybenko]

Hi,
are you getting an error? if Channel_Number is a text field then filter
should be (value in quotes):

strFilter = "Channel_Number = '" & Me!Channel_Number & "'"

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Marshall Barton]

I have a form that I fill out that has 2 fields - PSN_Number &
Channel_Number. What I want to achieve is for another field to auto populate
with data from a 3rd field (Degraded_Serial_Number) which is dependant on
what is entered in the 2 fields above. I have a table that corresponds this
info - HDD_List. No matter what I have tried, I cannot get it to work.
Below is the code that I am using:

Private Sub Channel_Number_AfterUpdate()
On Error GoTo Err_Channel_Number_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "Channel_Number = " & Me!Channel_Number

' Look up HDD Serial Number and assign it to Degraded Serial Number
control.
Me!Degraded_Serial_Number = DLookup("HDD_Serial_Number", "HDD_List",
strFilter)

Exit_Channel_Number_AfterUpdate:
Exit Sub

Err_Channel_Number_AfterUpdate:
MsgBox Err.Description
Resume Exit_Channel_Number_AfterUpdate

End Sub

Adding the criteria to the DLookup line is where I am lost. Hopefully
someone can point me in the right direction.

Depends on the data type of the fields in the criteria.
What you have should work for a number type Channel_Number
field.

Your code only uses the one field, but you said you wanted
to use two fields. Assuming the PSN_Number field is also a
number type field, try using:

strFilter = "Channel_Number = " & Me!Channel_Number _
& " AND PSN_Number = " & Me!PSN_Number

If both fields are Text fields, then that would beL

strFilter = "Channel_Number = """ & Me!Channel_Number _
& """ AND PSN_Number = """ & Me!PSN_Number & """"

 

[Leo S.]

Thank you for your help Alex. Not getting an error....mind you the code is
incomplete. I realize that my explanation below is a little unclear. Here
is what I have:

A form Named "Add_New_Incident" with the following fields:
"PSN_Number"
"Channel_Number"
"Degraded_Serial_Number" - this is the one I want to auto populate

I have a table called "HDD_List" which has 3 fields:
"PSN_Number"
"Channel_Number"
"HDD_Serial_Number" - this is where I want to get the data for the field
above from

So what I basically want , is when I input a PSN # and a Channel # in to my
form, it will go and look at the table and populate the
"Degraded_Serial_Number" field with the corresponding Serial Number.

Example - I enter PSN 5, Channel 7....HDD_List table shows the serial # as
123456.

LOL...I hope that makes it easy to understand.

Thanks again

 

[Leo S.]

Thanks very much Marshall. You're awesome. That was the fix I was looking
for.