Need Help on Lookup Fields

[shep]

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

[Ofer]

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

 

['69 Camaro]

Hi, shep.

Need: How can I get the Patient name to fill in when I select ChartNumber?

If you have primary keys on these tables, then I would recommend an
autolookup query to automatically fill in any and all information about the
patient (like the phone number, too) on this appointment form which doesn't
need any coding. There's a step-by-step tutorial on how to automatically
fill in the information on a form on this Web page:

http://www.Access.QBuilt.com/html/forms.html#AutoCompleteForm

Substitute your appointments table for the tblInvoices table in the example
and substitute your patients table for the tblContractors table in the
example. (I would use the primary key of the patient table instead of the
ChartNumber -- if that isn't your primary key -- to be on the safe side, but
since it's unique you may get away with using ChartNumber.) When creating
the query, make sure that the ChartNumber (or patient's table primary key) is
added to the query design grid from your appointments table, not your
patients table.

When complete, all you have to do when creating a new appointment is to
select the patient's name (or the ChartNumber, depending upon which value(s)
you want to show) in the combo box and the patient's information will
automatically populate all of the patient fields on the form. No code
necessary.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[shep]

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

[Ofer]

On the after update event of the ChartNumber field write the code
Me.[Name] = Dlookup("[Name]",
"[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")

Or on the ControlSource Property of the [Name] field write
=Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"")

On the after update of the ChartNumber field write the code (in the code
section)
Me.[Name].Requery

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

[Ofer]

When you look at the poperties box, in the after update event, dont put the
code in this line, on the right side there is a button, with three dots,
click on it, and select code.
In the code section insert the code.

Now I'm not sure if you got your answer from Gunny already.

Thanks again, I think I am getting close.

I entered the code you provided on the After Update event of ChartNumber.

I deleted the row source in the Name properties.

When entered new record, Chartnumber enters but I get msg, " Can't find
Macro Me. Doesn't exist or has not been saved. I did save changes to form.

How should I save the Macro?

Thanks for your help and your patience!

On the after update event of the ChartNumber field write the code
Me.[Name] = Dlookup("[Name]",
"[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")

Or on the ControlSource Property of the [Name] field write
=Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"")

On the after update of the ChartNumber field write the code (in the code
section)
Me.[Name].Requery

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

:

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

:

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

[shep]

Thanks for your help again. I will benefit from your assistance.

 

[shep]

Thanks again, I think I am getting close.

I entered the code you provided on the After Update event of ChartNumber.

I deleted the row source in the Name properties.

When entered new record, Chartnumber enters but I get msg, " Can't find
Macro Me. Doesn't exist or has not been saved. I did save changes to form.

How should I save the Macro?

Thanks for your help and your patience!

On the after update event of the ChartNumber field write the code
Me.[Name] = Dlookup("[Name]",
"[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")

Or on the ControlSource Property of the [Name] field write
=Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"")

On the after update of the ChartNumber field write the code (in the code
section)
Me.[Name].Requery

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

:

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

[shep]

That wants to work, but I am not entering something correctly. Here is code
I entered:
Private Sub ChartNumber_AfterUpdate()
Me.[Name] = DLookup("[Name]", "[tblAppointmentsV1]", " ChartNumber=" &
Me.ChartNumber & "")
Me.[Name].Requery
End Sub

When I select a ChartNumber from combo box, I get this msg:
Invalid qualifier
Qualifiers are used for disambiguation. This error has the following cause
and solution:
• The qualifier does not identify a project, module, object, or a variable
of user-defined type within the current scope.
Check the spelling of the qualifier. Make sure that the qualifying
identifier is within the current scope. For example, a variable of
user-defined type in a Private module is visible only within that module.

The dot after Me and [Name] before .Requery are highlighted

I did read Gunny's response and will try what he recommended. I would like
to get your instruction working first. Obviously I am not a programmer, just
an old retired guy trying to help the Epilepsy Foundation in San Antonio -
pro bono. So your effort to help me is for a good cause.

Thanks

When you look at the poperties box, in the after update event, dont put the
code in this line, on the right side there is a button, with three dots,
click on it, and select code.
In the code section insert the code.

Now I'm not sure if you got your answer from Gunny already.

Thanks again, I think I am getting close.

I entered the code you provided on the After Update event of ChartNumber.

I deleted the row source in the Name properties.

When entered new record, Chartnumber enters but I get msg, " Can't find
Macro Me. Doesn't exist or has not been saved. I did save changes to form.

How should I save the Macro?

Thanks for your help and your patience!

On the after update event of the ChartNumber field write the code
Me.[Name] = Dlookup("[Name]",
"[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")

Or on the ControlSource Property of the [Name] field write
=Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"")

On the after update of the ChartNumber field write the code (in the code
section)
Me.[Name].Requery

:

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

:

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

:

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

['69 Camaro]

You're welcome. I didn't mention it explicitly, but the tutorial shows how
to create the query, then create a form based on it, but you don't need to
create an entirely new form. You could set your form's RecordSource
Property to the name of this query (as long as the autolookup query has the
same fields included as your former RecordSource Property).

And the reason you are having trouble with the code is because you designed
your table using a Reserved word, Name. If you don't enjoy banging your
head against the wall while screaming, "Why won't this work?!!" then
_always_ follow these rules:

1.) Never use Reserved words to name anything (tables, fields, variables,
procedures, et cetera). For lists of the Reserved words to avoid, please
see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

When reading this second page, pay particular attention to the myth on that
page because you now know why it's not true:

"For existing objects with names that contain reserved words, you can avoid
errors by surrounding the object name with brackets ([ ])."

2.) Always rename the controls created with the Form Wizard so that the
control's name is different than the field name. For example, LastName
becomes txtLastName for the Text Box control bound to the LastName field.

3.) Never use spaces or other non-alphanumeric characters to name things.
The one exception is the underscore. If you want things to look
aesthetically pleasing, then set the field's Caption Property while the
table is in Design View to display whatever you would like, including spaces
and special characters. When you create new controls, the Caption Property
is used for the attached label. Headers on tables and queries will also
show the Caption Property instead of the field name, but only if the Caption
Property has been set.

4.) Always refer to controls on the form in VBA code with Me! and not Me.
because the dot operator signifies a property of the form, not necessarily
the control by the same name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

 

[Ofer]

I missed the part where you said that you select the ChartNumber from a combo
box, if so add to the rowsource of the combo another field, the [name] field,
that way you wont need to access the table again.
On the after update event you can write the code

Me.[Name] = Me.[ChartNumber].column(1)

the column numbers of the combo start from 0, by the position of each column
in the recordset, assuming that the name is the second column.
Or you can write in the control source of the name field
=[ChartNumber].column(1)

That wants to work, but I am not entering something correctly. Here is code
I entered:
Private Sub ChartNumber_AfterUpdate()
Me.[Name] = DLookup("[Name]", "[tblAppointmentsV1]", " ChartNumber=" &
Me.ChartNumber & "")
Me.[Name].Requery
End Sub

When I select a ChartNumber from combo box, I get this msg:
Invalid qualifier
Qualifiers are used for disambiguation. This error has the following cause
and solution:
• The qualifier does not identify a project, module, object, or a variable
of user-defined type within the current scope.
Check the spelling of the qualifier. Make sure that the qualifying
identifier is within the current scope. For example, a variable of
user-defined type in a Private module is visible only within that module.

The dot after Me and [Name] before .Requery are highlighted

I did read Gunny's response and will try what he recommended. I would like
to get your instruction working first. Obviously I am not a programmer, just
an old retired guy trying to help the Epilepsy Foundation in San Antonio -
pro bono. So your effort to help me is for a good cause.

Thanks

When you look at the poperties box, in the after update event, dont put the
code in this line, on the right side there is a button, with three dots,
click on it, and select code.
In the code section insert the code.

Now I'm not sure if you got your answer from Gunny already.

Thanks again, I think I am getting close.

I entered the code you provided on the After Update event of ChartNumber.

I deleted the row source in the Name properties.

When entered new record, Chartnumber enters but I get msg, " Can't find
Macro Me. Doesn't exist or has not been saved. I did save changes to form.

How should I save the Macro?

Thanks for your help and your patience!

:

On the after update event of the ChartNumber field write the code
Me.[Name] = Dlookup("[Name]",
"[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")

Or on the ControlSource Property of the [Name] field write
=Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"")

On the after update of the ChartNumber field write the code (in the code
section)
Me.[Name].Requery

:

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

:

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

:

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

[shep]

Thanks again for all your help. Both of you have given me good advice. I
think I need to redo a couple of tables in a separate dBase to work with and
successfully implement what y'all have taught me. Then I can implement it in
the Patient Care dBase. I'll advise in a day or two on my progress.

Thanks to all of you who give of your time and talent to help others - you
will be rewarded.

You're welcome. I didn't mention it explicitly, but the tutorial shows how
to create the query, then create a form based on it, but you don't need to
create an entirely new form. You could set your form's RecordSource
Property to the name of this query (as long as the autolookup query has the
same fields included as your former RecordSource Property).

And the reason you are having trouble with the code is because you designed
your table using a Reserved word, Name. If you don't enjoy banging your
head against the wall while screaming, "Why won't this work?!!" then
_always_ follow these rules:

1.) Never use Reserved words to name anything (tables, fields, variables,
procedures, et cetera). For lists of the Reserved words to avoid, please
see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

When reading this second page, pay particular attention to the myth on that
page because you now know why it's not true:

"For existing objects with names that contain reserved words, you can avoid
errors by surrounding the object name with brackets ([ ])."

2.) Always rename the controls created with the Form Wizard so that the
control's name is different than the field name. For example, LastName
becomes txtLastName for the Text Box control bound to the LastName field.

3.) Never use spaces or other non-alphanumeric characters to name things.
The one exception is the underscore. If you want things to look
aesthetically pleasing, then set the field's Caption Property while the
table is in Design View to display whatever you would like, including spaces
and special characters. When you create new controls, the Caption Property
is used for the attached label. Headers on tables and queries will also
show the Caption Property instead of the field name, but only if the Caption
Property has been set.

4.) Always refer to controls on the form in VBA code with Me! and not Me.
because the dot operator signifies a property of the form, not necessarily
the control by the same name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

Thanks for your help again. I will benefit from your assistance.

 

[shep]

That did it!! I want to thank you again for giving your time and talent to
help others. And I appreciate your patience.

You have made me a happy man today.

I missed the part where you said that you select the ChartNumber from a combo
box, if so add to the rowsource of the combo another field, the [name] field,
that way you wont need to access the table again.
On the after update event you can write the code

Me.[Name] = Me.[ChartNumber].column(1)

the column numbers of the combo start from 0, by the position of each column
in the recordset, assuming that the name is the second column.
Or you can write in the control source of the name field
=[ChartNumber].column(1)

That wants to work, but I am not entering something correctly. Here is code
I entered:
Private Sub ChartNumber_AfterUpdate()
Me.[Name] = DLookup("[Name]", "[tblAppointmentsV1]", " ChartNumber=" &
Me.ChartNumber & "")
Me.[Name].Requery
End Sub

When I select a ChartNumber from combo box, I get this msg:
Invalid qualifier
Qualifiers are used for disambiguation. This error has the following cause
and solution:
• The qualifier does not identify a project, module, object, or a variable
of user-defined type within the current scope.
Check the spelling of the qualifier. Make sure that the qualifying
identifier is within the current scope. For example, a variable of
user-defined type in a Private module is visible only within that module.

The dot after Me and [Name] before .Requery are highlighted

I did read Gunny's response and will try what he recommended. I would like
to get your instruction working first. Obviously I am not a programmer, just
an old retired guy trying to help the Epilepsy Foundation in San Antonio -
pro bono. So your effort to help me is for a good cause.

Thanks

When you look at the poperties box, in the after update event, dont put the
code in this line, on the right side there is a button, with three dots,
click on it, and select code.
In the code section insert the code.

Now I'm not sure if you got your answer from Gunny already.


:

Thanks again, I think I am getting close.

I entered the code you provided on the After Update event of ChartNumber.

I deleted the row source in the Name properties.

When entered new record, Chartnumber enters but I get msg, " Can't find
Macro Me. Doesn't exist or has not been saved. I did save changes to form.

How should I save the Macro?

Thanks for your help and your patience!

:

On the after update event of the ChartNumber field write the code
Me.[Name] = Dlookup("[Name]",
"[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")

Or on the ControlSource Property of the [Name] field write
=Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"")

On the after update of the ChartNumber field write the code (in the code
section)
Me.[Name].Requery

:

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

:

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

:

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks

 

[shep]

It works!! Thanks again for your help!

You're welcome. I didn't mention it explicitly, but the tutorial shows how
to create the query, then create a form based on it, but you don't need to
create an entirely new form. You could set your form's RecordSource
Property to the name of this query (as long as the autolookup query has the
same fields included as your former RecordSource Property).

And the reason you are having trouble with the code is because you designed
your table using a Reserved word, Name. If you don't enjoy banging your
head against the wall while screaming, "Why won't this work?!!" then
_always_ follow these rules:

1.) Never use Reserved words to name anything (tables, fields, variables,
procedures, et cetera). For lists of the Reserved words to avoid, please
see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

When reading this second page, pay particular attention to the myth on that
page because you now know why it's not true:

"For existing objects with names that contain reserved words, you can avoid
errors by surrounding the object name with brackets ([ ])."

2.) Always rename the controls created with the Form Wizard so that the
control's name is different than the field name. For example, LastName
becomes txtLastName for the Text Box control bound to the LastName field.

3.) Never use spaces or other non-alphanumeric characters to name things.
The one exception is the underscore. If you want things to look
aesthetically pleasing, then set the field's Caption Property while the
table is in Design View to display whatever you would like, including spaces
and special characters. When you create new controls, the Caption Property
is used for the attached label. Headers on tables and queries will also
show the Caption Property instead of the field name, but only if the Caption
Property has been set.

4.) Always refer to controls on the form in VBA code with Me! and not Me.
because the dot operator signifies a property of the form, not necessarily
the control by the same name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

Thanks for your help again. I will benefit from your assistance.

 

['69 Camaro]

You're welcome! Glad it's working!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

It works!! Thanks again for your help!

You're welcome. I didn't mention it explicitly, but the tutorial shows
how
to create the query, then create a form based on it, but you don't need
to
create an entirely new form. You could set your form's RecordSource
Property to the name of this query (as long as the autolookup query has
the
same fields included as your former RecordSource Property).

And the reason you are having trouble with the code is because you
designed
your table using a Reserved word, Name. If you don't enjoy banging your
head against the wall while screaming, "Why won't this work?!!" then
_always_ follow these rules:

1.) Never use Reserved words to name anything (tables, fields,
variables,
procedures, et cetera). For lists of the Reserved words to avoid, please
see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

When reading this second page, pay particular attention to the myth on
that
page because you now know why it's not true:

"For existing objects with names that contain reserved words, you can
avoid
errors by surrounding the object name with brackets ([ ])."

2.) Always rename the controls created with the Form Wizard so that the
control's name is different than the field name. For example, LastName
becomes txtLastName for the Text Box control bound to the LastName field.

3.) Never use spaces or other non-alphanumeric characters to name
things.
The one exception is the underscore. If you want things to look
aesthetically pleasing, then set the field's Caption Property while the
table is in Design View to display whatever you would like, including
spaces
and special characters. When you create new controls, the Caption
Property
is used for the attached label. Headers on tables and queries will also
show the Caption Property instead of the field name, but only if the
Caption
Property has been set.

4.) Always refer to controls on the form in VBA code with Me! and not
Me.
because the dot operator signifies a property of the form, not
necessarily
the control by the same name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)

Thanks for your help again. I will benefit from your assistance.

:

Hi, shep.

Need: How can I get the Patient name to fill in when I select
ChartNumber?

If you have primary keys on these tables, then I would recommend an
autolookup query to automatically fill in any and all information
about
the
patient (like the phone number, too) on this appointment form which
doesn't
need any coding. There's a step-by-step tutorial on how to
automatically
fill in the information on a form on this Web page:

http://www.Access.QBuilt.com/html/forms.html#AutoCompleteForm

Substitute your appointments table for the tblInvoices table in the
example
and substitute your patients table for the tblContractors table in the
example. (I would use the primary key of the patient table instead of
the
ChartNumber -- if that isn't your primary key -- to be on the safe
side,
but
since it's unique you may get away with using ChartNumber.) When
creating
the query, make sure that the ChartNumber (or patient's table primary
key) is
added to the query design grid from your appointments table, not your
patients table.

When complete, all you have to do when creating a new appointment is
to
select the patient's name (or the ChartNumber, depending upon which
value(s)
you want to show) in the combo box and the patient's information will
automatically populate all of the patient fields on the form. No code
necessary.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember
that
questions answered the quickest are often from those who have a
history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to
create
Name
such as Joe J. Doe from the table's LastName, FirstName, MI and
included
ChartNumber from the table in the query. ChartNumber is unique to
each
patient.

I have a table to management appointments. It includes ChartNumber
and
Name
and they are lookups from the query. I created a form to enter
appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name
from
the lookup list.
Need: How can I get the Patient name to fill in when I select
ChartNumber?

Thanks

 

[Ofer]

Thank you

That did it!! I want to thank you again for giving your time and talent to
help others. And I appreciate your patience.

You have made me a happy man today.

I missed the part where you said that you select the ChartNumber from a combo
box, if so add to the rowsource of the combo another field, the [name] field,
that way you wont need to access the table again.
On the after update event you can write the code

Me.[Name] = Me.[ChartNumber].column(1)

the column numbers of the combo start from 0, by the position of each column
in the recordset, assuming that the name is the second column.
Or you can write in the control source of the name field
=[ChartNumber].column(1)

That wants to work, but I am not entering something correctly. Here is code
I entered:
Private Sub ChartNumber_AfterUpdate()
Me.[Name] = DLookup("[Name]", "[tblAppointmentsV1]", " ChartNumber=" &
Me.ChartNumber & "")
Me.[Name].Requery
End Sub

When I select a ChartNumber from combo box, I get this msg:
Invalid qualifier
Qualifiers are used for disambiguation. This error has the following cause
and solution:
• The qualifier does not identify a project, module, object, or a variable
of user-defined type within the current scope.
Check the spelling of the qualifier. Make sure that the qualifying
identifier is within the current scope. For example, a variable of
user-defined type in a Private module is visible only within that module.

The dot after Me and [Name] before .Requery are highlighted

I did read Gunny's response and will try what he recommended. I would like
to get your instruction working first. Obviously I am not a programmer, just
an old retired guy trying to help the Epilepsy Foundation in San Antonio -
pro bono. So your effort to help me is for a good cause.

Thanks

:

When you look at the poperties box, in the after update event, dont put the
code in this line, on the right side there is a button, with three dots,
click on it, and select code.
In the code section insert the code.

Now I'm not sure if you got your answer from Gunny already.


:

Thanks again, I think I am getting close.

I entered the code you provided on the After Update event of ChartNumber.

I deleted the row source in the Name properties.

When entered new record, Chartnumber enters but I get msg, " Can't find
Macro Me. Doesn't exist or has not been saved. I did save changes to form.

How should I save the Macro?

Thanks for your help and your patience!

:

On the after update event of the ChartNumber field write the code
Me.[Name] = Dlookup("[Name]",
"[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")

Or on the ControlSource Property of the [Name] field write
=Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"")

On the after update of the ChartNumber field write the code (in the code
section)
Me.[Name].Requery

:

Thanks for your response.

Here is what I put in Row Source for Name:
Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"")
I get msg that source does not exist. I probably have not entered
correctly. Can you please advise me further?

Thanks

:

Use Dlookup

' If ChartNumber is Number
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = "
& Me.ChartNumber)

' If ChartNumber is String
Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '"
& Me.ChartNumber & "'")

:

I am running WIN XP Pro and Office 2003 Pro

I have I have a table with patients info. I created a query to create Name
such as Joe J. Doe from the table's LastName, FirstName, MI and included
ChartNumber from the table in the query. ChartNumber is unique to each
patient.

I have a table to management appointments. It includes ChartNumber and Name
and they are lookups from the query. I created a form to enter appointment
data into the appointment table.

Problem: On the form, I have to pick ChartNumber and then pick Name from
the lookup list.
Need: How can I get the Patient name to fill in when I select ChartNumber?

Thanks