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

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


"Leo S." <Leo S.@discussions.microsoft.com> wrote in message
news:F4FB61FF-C75C-4266-AC4B-(E-Mail Removed)...
> 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

Leo S. <Leo S.@discussions.microsoft.com> wrote:

>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 & """"

--
Marsh
MVP [MS Access]

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

"Alex Dybenko" wrote:

> 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
>
>
> "Leo S." <Leo S.@discussions.microsoft.com> wrote in message
> news:F4FB61FF-C75C-4266-AC4B-(E-Mail Removed)...
> > 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
>
>

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

"Marshall Barton" wrote:

> Leo S. <Leo S.@discussions.microsoft.com> wrote:
>
> >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 & """"
>
> --
> Marsh
> MVP [MS Access]
>