Reference a table field?

[Guest]

I have created a simple form with a textbox and button, you enter a patient
identifier number in the text box and click the button. If the patient exists
the record in the underlying patient data table is shown, if not I want it to
display a message saying that. As it stands when the patient is not there it
just highlights the top record in the table. I need to be able to reference
the PatID field in the table, Patients, to set the code up properly. I know
the way with Forms to access a field but I can't seem to get the syntax for
specifying a particular table field. Any ideas?

Thanks in advance,
Ian.

 

[Al Camp]

Ian,
One minor caveat... using just Name to find a record may work OK now, but
can lead to problems in the future. It's better to use a "key" unique value
to find records.

Take the value that the user types into the text box and use it to do a
DLookup against the table.
(aircode)
If Dlookup of the value is NULL Then
Post a message to the user
Exit the sub
Else
Open the form using the value from your form as the "Where" argument
of the OpenForm command.

But... an even better solution would be to use a combobox instead of a
text control to get the Name input from the user. The combobox would be
based against ALL the legitimate Name values in your table already, so
there's no way the user can choose a non-existent Name, or do a typo.
So... every Name listed would be a "hit".
That combo value would be used just like the text control value, to open
the form to the correct record.

Better than build code to fix mistakes... build code so mistakes can't
happen.
hth
Al Camp

 

[Guest]

Al,

Thanks for your suggestions. The combobox does sound like an excellent
solution, could I trouble you to explain how the selection in the combobox
would then open that record on the form.

Thanks for your help,
Ian.

 

[Al Camp]

As I mentioned, use the value in the combo just the way you would have with
your text control.
example...
If you had a form named frmDialogForm with a combobox named [cboClientName]
and you are opening frmClients with a field called [ClientName]...

DoCmd.OpenForm "frmClients", , , "[ClientName] =
Forms!frmDialogForm!cboClientName"

hth
Al Camp

I'm using the WHERE argument of the OpenForm method to indicate what record
I want to Open on.

 

[Guest]

Hi Al,

Sorry for the delay in replying. Thanks for your solution, that works great
for me, cheers,

Ian.

As I mentioned, use the value in the combo just the way you would have with
your text control.
example...
If you had a form named frmDialogForm with a combobox named [cboClientName]
and you are opening frmClients with a field called [ClientName]...

DoCmd.OpenForm "frmClients", , , "[ClientName] =
Forms!frmDialogForm!cboClientName"

hth
Al Camp

I'm using the WHERE argument of the OpenForm method to indicate what record
I want to Open on.

Al,

Thanks for your suggestions. The combobox does sound like an excellent
solution, could I trouble you to explain how the selection in the combobox
would then open that record on the form.

Thanks for your help,
Ian.

 

[Guest]

Hi Al,

That solution worked great but having used it I got to wondering about
instead of having the first form open a second form using the DoCmd.OpenForm,
it would be nice to have the second form embedded as a subform onto the first
so that the details relating to my input were more elegantly visible.
However, now I'm not sure of how to replace the original DoCmd.OpenForm with
something to update the subform data, can you help?

Thanks again for your help,
Ian.

Hi Al,

Sorry for the delay in replying. Thanks for your solution, that works great
for me, cheers,

Ian.

As I mentioned, use the value in the combo just the way you would have with
your text control.
example...
If you had a form named frmDialogForm with a combobox named [cboClientName]
and you are opening frmClients with a field called [ClientName]...

DoCmd.OpenForm "frmClients", , , "[ClientName] =
Forms!frmDialogForm!cboClientName"

hth
Al Camp

I'm using the WHERE argument of the OpenForm method to indicate what record
I want to Open on.

Al,

Thanks for your suggestions. The combobox does sound like an excellent
solution, could I trouble you to explain how the selection in the combobox
would then open that record on the form.

Thanks for your help,
Ian.


:

Ian,
One minor caveat... using just Name to find a record may work OK now,
but
can lead to problems in the future. It's better to use a "key" unique
value
to find records.

Take the value that the user types into the text box and use it to do
a
DLookup against the table.
(aircode)
If Dlookup of the value is NULL Then
Post a message to the user
Exit the sub
Else
Open the form using the value from your form as the "Where"
argument
of the OpenForm command.

But... an even better solution would be to use a combobox instead of
a
text control to get the Name input from the user. The combobox would be
based against ALL the legitimate Name values in your table already, so
there's no way the user can choose a non-existent Name, or do a typo.
So... every Name listed would be a "hit".
That combo value would be used just like the text control value, to
open
the form to the correct record.

Better than build code to fix mistakes... build code so mistakes
can't
happen.
hth
Al Camp


I have created a simple form with a textbox and button, you enter a
patient
identifier number in the text box and click the button. If the patient
exists
the record in the underlying patient data table is shown, if not I want
it
to
display a message saying that. As it stands when the patient is not
there
it
just highlights the top record in the table. I need to be able to
reference
the PatID field in the table, Patients, to set the code up properly. I
know
the way with Forms to access a field but I can't seem to get the syntax
for
specifying a particular table field. Any ideas?

Thanks in advance,
Ian.