Display value based on input

[Guest]

I'm using a form to track a QA process for a large number of records. These
records have both an id number and record name. I'm need to have the record
name display based on inputing the id number.

I've tried using DLookUp as an expression and VB code after reading other
posts, but it won't return the value when inserted as an on change or on lost
focus (and a couple others). The QA results are being stored in one table.
The record ID and name are stored in a different table. I've set the record
ID field up to use a standard lookup function. If there was a way to display
both the ID and name as a result of a standard lookup instead of just one,
that'd work, too.

Thanks for your help

 

[Guest]

The DLookup will do what you are trying to do. It is just a matter of
getting the syntax and references correct. As to where to do it, the After
Update event would be the best. Be very careful about using the Change
event. The Change event behaves exactly as it is named. It fires after
every change in the control, so if you type 3 characters in the control, the
Change event fires 3 times. Once after each keystroke. I don't know what
your controls are named or the names of your fields, but this is an example
of what it should be:

txtRecordID - The control where you put the record ID
txtRecordName - The control where you want to display the name

In the After Update event of txtRecordID

Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = " & Me.txtRecordID)

Note the above syntax assumes RecordID is a numeric field. In the DLookup,
like all other Domain Aggregate functions and SQL, delimiters appropriate to
the data type of the field being referenced are required.

Numeric - No Delimiters
Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = " & Me.txtRecordID)

Text - Single or Doulbe Quotes
Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = '" & Me.txtRecordID & "'")

Date - Pound sign
Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = #" & Me.txtRecordID & "#")

If this does not solve the problem, post back the code you are trying to use.

 

[Guest]

Thanks this set me on the path to get what I needed and got me moving again.

The DLookup will do what you are trying to do. It is just a matter of
getting the syntax and references correct. As to where to do it, the After
Update event would be the best. Be very careful about using the Change
event. The Change event behaves exactly as it is named. It fires after
every change in the control, so if you type 3 characters in the control, the
Change event fires 3 times. Once after each keystroke. I don't know what
your controls are named or the names of your fields, but this is an example
of what it should be:

txtRecordID - The control where you put the record ID
txtRecordName - The control where you want to display the name

In the After Update event of txtRecordID

Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = " & Me.txtRecordID)

Note the above syntax assumes RecordID is a numeric field. In the DLookup,
like all other Domain Aggregate functions and SQL, delimiters appropriate to
the data type of the field being referenced are required.

Numeric - No Delimiters
Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = " & Me.txtRecordID)

Text - Single or Doulbe Quotes
Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = '" & Me.txtRecordID & "'")

Date - Pound sign
Me.txtRecordName = DLookup("[RecordName]", "TheDifferentTable",
"[RecordID] = #" & Me.txtRecordID & "#")

If this does not solve the problem, post back the code you are trying to use.

I'm using a form to track a QA process for a large number of records. These
records have both an id number and record name. I'm need to have the record
name display based on inputing the id number.

I've tried using DLookUp as an expression and VB code after reading other
posts, but it won't return the value when inserted as an on change or on lost
focus (and a couple others). The QA results are being stored in one table.
The record ID and name are stored in a different table. I've set the record
ID field up to use a standard lookup function. If there was a way to display
both the ID and name as a result of a standard lookup instead of just one,
that'd work, too.

Thanks for your help