Access Form troubles

[aussie]

I am having problems with form design in access2007.

My control source is "Patients Detail Form"
My control is MedicareNo.

In design view I create a form using Form Design, I add in a text box and under Properties, Control Source, I add the following
=[Patient Details Table]![MedicareNo]

When I go to Form View, I get an error in the input box : #Name?

I thought I could get around the problem by selecting Form (on create tab), and letting Access make the form automatically, which it did, and when I went to Form View, I was able to put in data without getting the #Name? error. However, when I went back to change the design of the form :
I tried to make a text box smaller, and all the text boxes in the form adjusted, I was not able to select only one for resize.

I was able to move text boxes up and down (to change the order), but was not able to move text boxes next to each other (side by side).

Can anyone help me?

 

[Bryan]

Unless I'm misunderstanding you . . .

By "control source" do you mean the name of the table the form is based upon? i.e "Patients Detail Form"

If that's the case, the table name is incorrect in your control's reference. It should be:
=[Patients Detail Form]![MedicareNo]

If "Patients Detail Form" is the name of your form, disregard this and post the name of the table.

What was the control source that worked when you used the wizard to design the form?

 

[aussie]

Thanks Bryan.
The "control source" refers to the text/combo box that I am working with, in this instance "MedicareNo"

The table name is "Patient Details Table".

I have compared this text box to other ones, and I can't see anything wrong. Perhaps I can't see the woods from the trees.

 

[rodmc]

A wee pointer, try and keep spaces out of your object names, makes things a whole lot easier down the line

 

[Bryan]

Thanks Bryan.
The "control source" refers to the text/combo box that I am working with, in this instance "MedicareNo"

The table name is "Patient Details Table".

Maybe I'm not understanding what you're trying to do, but if your form's record source is the "Patient Details Table" and "MedicareNo" is a field in that table, the control source of the text box should just be "MedicareNo".

BTW, what is the control you're working with? You stated text/combo box. Those are two different objects. It's one or the other.

 

[aussie]

Thanks Romc - I have realised that now, but am too far into the db to change it. Fortunately it is not a very big database, so I will get away with the spaces this time.

 

[aussie]

Hi Bryan,
I am setting the control source as MedicarNo. I am also making combo boxes on the form and am getting the same error.

On my combo box for Australian State form I have :
SELECT [Australian States Table].State FROM [Australian States Table];
this combo box works, but in my new form (Appointments form)
My Control Source for the combo box is "PatientFirstName"
So I have put in the following :
SELECT [Appointments Query].PatientFirstName FROM [Appointments Query];
(the query is made up from the "Appointments Table"

I really appreciate your help, hopefully you will be able to help me sort this out because it is doing my head in, and not to mention that I have a time limite to get this database up and running.

 

[Bryan]

Why don't we fix the text box first?

Delete the existing text box and add a new one. It will be unbound. Highlight the new text box (not its label). Go to the properties sheet, select the drop-down and then select "MedicareNo". Don't type in anything, just select what you're offered.

Save the form and see if the #name error went away.

 

[aussie]

Hi Bryan,
Thanks for your help. Your suggestion worked.
I am having another problem with a combo box.
I have put PatientFirstName as the control key.
Then I want it to display the following from Appointments Query :
PatientFirstName, PatientLastName, MedicareNo

I have got the combo box to display PatientFirstName, but it won't display the PatientLastName and MedicareNo.

PatientLastName and Medicare are not bound objects, they are purely so that the user can view the details to help them selecting from the combo box.

My row source is as follows :

SELECT [Appointments Query].PatientFirstName, [Appointments Query].PatientMiddleName, [Appointments Query].PatientLastName, [Appointments Query].MedicareNo FROM [Appointments Query];

 

[Bryan]

Delete the combo box and run the combo box wizard again. Make sure that you select all four fields to include in the combo picks.

 

[aussie]

Hi Bryan,
I deleted the combo box and then used the wizard again, but still came up with the same problem. After many hours of troubleshooting, I found that it was as simple as : Under Properties, Format, Change Column Count to the number of fields you want to show in the combo box!
Thanks very much for all your help

 

[aussie]

Hi Bryan,
Just when I correct one thing, another goes wrong.
I am working in Appointments Form. I have set up the following Row Source:

SELECT [Appointments Query].PatientFirstName, [Appointments Query].PatientLastName, [Appointments Query].PatientDOB FROM [Appointments Query];

When entering into the form, I pull the arrow down (combo box), and it shows the following columns :
PatientFirstName, PatientLastName, and PatientDOB - so far so good. However when I check the Appointments Table, DOB is being placed where PatientFirstName should go.

My Control Source is PatientFirstName
Any ideas ???
Thanks, Carolyn

 

[Bryan]

Presumably, you have an After Update Event that populates fields on the form from data selected in the combo box. This code maps the combo box picks to the fields on the form. It sounds as if something is mis-mapped. It should look something like this:

Private Sub cboFileNumber_AfterUpdate()
  
    Me.txtFileNumber = Me.cboFileNumber.Column(0)
    Me.txtRate = Me.cboFileNumber.Column(2)
    Me.txtClient = Me.cboFileNumber.Column(4)
    Me.txtClientAlpha = Me.cboFileNumber.Column(5)
    Me.txtMatter = Me.cboFileNumber.Column(1)
          
End Sub

For example, the value in Column(0) from the combo control is being placed into the txtFileNumber field on the form.

Check yours and change the column numbers to match the fields on your form.

 

[aussie]

Hi Bryan. It took time, but I eventually solved my problem with the following :

SELECT [Patient Details Table].PatientLastName, [Appointments Query].PatientLastName, [Appointments Query].PatientFirstName, [Appointments Query].PatientDOB, [Appointments Query].MedicareNo FROM [Patient Details Table] INNER JOIN [Appointments Query] ON [Patient Details Table].MedicareNo=[Appointments Query].MedicareNo;

Thank you so much for your help, it is much appreciated.