DLookup Missing RecordSource Question

[Tal]

Thanks in advance for all help received!!

I have a combo box that I want to populate based on the current record but
with data from a different table. So I have created a DLookup (only one so
far as a test) and I am getting a strange almost oxymoronic error message,
because it's getting the value, (which is listed in the error message!!!),
but doesn't think it's getting the value. I don't know if it matters, but the
tblClients is a linked table.

Help!!

Here's my VBA:

Private Sub cboKeyDinnerStatus_AfterUpdate()
Dim dlookClientLastName As String

dlookClientLastName = DLookup("[txtClientLastName]", "tblClients",
"[keyClient] = " & Me.keyClient)

Me.cboReceiptTo.RowSource = dlookClientLastName
Me.cboReceiptTo.Requery
End Sub

And here's the crazy error message:

The record source 'Adler' specified on this form or report does not exist.

The name of the recordsource may be misspelled, the recordsource was deleted
or renamed, or the recordsource exists in a different database.

In the Form or Report's Design view, display the property sheet by clicking
the Properties button, and then set the RecordSource property to an existing
table or query.

 

[Ray]

The problem is that after the DlookUp, your variable (dlookClientLastName)
has the value "Adler". You are then assigning the string "Adler" as the
RowSource of your combo box. The RowSource needs to be either a table name
or a valid query.
It's difficult to tell what you are trying to do, but maybe something like
this:

Me.cboReceiptTo.RowSource = "SELECT * FROM table where txtClientLastName =
'" &dlookClientLastName & "'"


Ray

 

[Tal]

Hi Ray,

Thank you. Thank you.
You made me realize that I had not defined my RowSourceType as "Value List"
Once I did that and set up an intermediary strReceiptTo, it worked!!

Cheers,
Tal

The problem is that after the DlookUp, your variable (dlookClientLastName)
has the value "Adler". You are then assigning the string "Adler" as the
RowSource of your combo box. The RowSource needs to be either a table name
or a valid query.
It's difficult to tell what you are trying to do, but maybe something like
this:

Me.cboReceiptTo.RowSource = "SELECT * FROM table where txtClientLastName =
'" &dlookClientLastName & "'"


Ray

Thanks in advance for all help received!!

I have a combo box that I want to populate based on the current record but
with data from a different table. So I have created a DLookup (only one so
far as a test) and I am getting a strange almost oxymoronic error message,
because it's getting the value, (which is listed in the error message!!!),
but doesn't think it's getting the value. I don't know if it matters, but the
tblClients is a linked table.

Help!!

Here's my VBA:

Private Sub cboKeyDinnerStatus_AfterUpdate()
Dim dlookClientLastName As String

dlookClientLastName = DLookup("[txtClientLastName]", "tblClients",
"[keyClient] = " & Me.keyClient)

Me.cboReceiptTo.RowSource = dlookClientLastName
Me.cboReceiptTo.Requery
End Sub

And here's the crazy error message:

The record source 'Adler' specified on this form or report does not exist.

The name of the recordsource may be misspelled, the recordsource was deleted
or renamed, or the recordsource exists in a different database.

In the Form or Report's Design view, display the property sheet by clicking
the Properties button, and then set the RecordSource property to an existing
table or query.