Control source or default?

[aussie]

I have field in a form called MedicareNo, based on a table (Appointments Table) where I have set the control source to a query. The entry is =[Appointments Medicare Query]![Patient Details Table.MedicareNo]
When this returned a #Name? error, I changed the control source to MedicareNo and put the entry into the default settings. This did not work either.
Have I got the brackets in the wrong place?? Should the control source be MedicareNo or should it be the =[Appointments Medicare Query]![Patient Details Table.MedicareNo] (with the correct format which I need help on.
Thankyou:banghead:

 

[liddlem]

Hi Aussie.
Sorry, I don't have an answer, but see a potential issues that will cause you endless problems as your programming skills improve.

Its standard practice to name tables and fields without spaces.(DBs dont like spaces in names)

Either use underscores (_) to join words like "Appointments_Medicare_Table" or
use CamelCase as in AppointmentsMedicareTable
This holds true for all objects - Tables, Queries, Forms, Reports, Fields, Macros, Variables, Procedure and Function names.

Also, you will find that if you keep the names as short as possible, it helps.
To this end, it is common practice for developers to use a prefix to identify objects as follows.
TblTableName for Tables
QryQueryName for queries
FrmFormName for forms
RptReportName for reports
McrMacroName for macros

Personally, (this is not a standard convention, but I find that it helps me) I also use
LkpTableName for tables that provide source (lookup) data and
MntFormName for forms that are used to maintain the lookup lists.
FnFunctionName() for functions
SubProcedureName() for (Sub) Procedures

Finally, to get back to your original problem, I am wondering whether MedicareNo in the source table is of a NUMBER type, but in the Patient Details Table, it is of TEXT type?

 

[Beetle]

You cannot directly set the Control Source of a form control (like a text box) to a field that is not included in the Record Source of the form.

You can either;

• Create a query that joins the necessary tables and includes all the fields you need, then use that query as the record source of your form. One caveat to this method; depending on the nature of the query, how many tables are involved and the type of joins used, this could end up being a read only record set. That may or may not matter depending on what the purpose of your form is.

-OR-

• Use a Domain function in the Control Source of the form Control to return the value from the other table, something like;

=DLookup("[MecidareNo]", "[Patient Details]", "[PatientID]=" & [PatientID])