Opening a form using multiple queries

[jpeat]

Hello,



I'm just a beginner at MS Access and was hoping someone could please assist me with an issue I've been working on the last couple of days.

I have created a form with multiple buttons on it (all of them are different job names). When the user clicks a certain button, I need it to take them to a second form and autopopulate 3 separate text boxes based on 3 separate queries (there are 3 queries per job name).

I've written the basic code to open the second form, but am confused about the following:

1. the record source for the second form given it will be pulling data from multiple queries; and
2. what code to write in the 'onclick' event procedure for each job name button on the first form.

Any guidance would be hugely appreciated.

Many thanks

[aytee111]

1. blank/empty
2. DoCmd.OpenForm "formname" - you could also just put it in a macro.

(I presume there is a reason for those three textboxes being on a different form, not on the original form.)

[jpeat]

Hi aytee111,

Thank you for your reply. I am still confused sorry!

1. Why would the record source be blank? How does my second form know where to pull the data from?
2. I've got the open form code, but am not sure what to write next. I need the onclick procedure to open the 2nd form and populate 3 text boxes (each based on a different query I've written).

At the moment I've just put the record source in the second form as 1 of the queries and bound one of the text boxes to one of the fields in the query. I want to create 2 other bound text boxes on this same form but they relate to different queries. Can I put multiple queries in the one form's record source?

Many thanks again.

[aytee111]

There are three textboxes on your second form with data coming from queries - what else is there on that form? The data in those textboxes comes from separate queries, that is where the data comes from, not from the record source, unless there are other fields on the form? You do not need a record source when there are no fields relating to it. All you need to do is open the form in OnClick and the form will populate itself based on your queries.

How do these queries populate the textboxes?

[jpeat]

Yes there are 3 text boxes on my second form and at this stage that is all as I have only just begun designing it and wanted to test these out first before adding anything else. I haven't figured out how to populate the 3 textboxes with the queries yet (I guess I was thinking the record source would help me achieve this but I now know that is not correct).

I know that I need to write some code in the OnClick event procedure to populate the text boxes, I'm just not sure what to write.

I tried the following in the OnClick procedure:

DoCmd.OpenForm "User Interface 2"
[Forms]![User Interface 2]![Job Name] = [Queries]![PM-01]![Job Name]

Where User Interface 2 = the second form
Job Name (in the left part of the equation) = the name of the first text box I want to populate in that second form
PM-01 = the query I want to use to populate the Job Name text box
Job Name (in the right part of the equation) = the specific field in the query I want to use to populate the Job Name text box

When I try and run that event I get the following error:
Runtime Error 2465: can't find the field "|" referred to in your expression
Am I referencing the query incorrectly or am I just not able to tell a text box to return a field value from a query?

Many thanks again for your assistance, it's greatly appreciated.

[aytee111]

These text boxes need to be re-thought. One doesn't commonly use queries to populate a textbox. Usually it is either a combo or a list box. The combo box would be empty but the query would be used for the user to select something from that query. A list box is used to display data, in which case the columns are all nicely displayed containing data other than that which would normally appear on the form, i.e. not related to the record source. This data can be selected but not modified. In either case they would automatically be populated when the form opens.

So ask yourself some questions - first and foremost, what is the form going to be used for. Inquiry only? Adding new records? Changing existing records? In the first instance, having a query as your record source would make your life a lot easier. In the other two cases, the record source would be a table. Often there are forms I create where the record source is empty - something like a menu form or a selection form with multiple subforms, etc. It is allowed and makes sense sometimes.

Back to question number 1 - decide what your form is going to do.