Form launches with values from first record in query

[aclambert]

Hi everyone

hoping you can help with this:

I'm designing a form in Access that displays customer account records from a query, based on the selection of a record from a combo box. This works well and is straight-forward.

My problem is that when the form launches it shows the details of the first record in the query before I even select anything from the combo box. Is there a way to stop this happening? I just want all the fields to be totally empty until a selection is made.

Thanks

Aaron

 

[randy700]

How about putting a default value in the combo box that doesn't relate to any record?


Randy

 

[fneily]

Create your main query. Then create another Union query. This has to be in SQL view. You can't do union queries in the grid. So change to SQLview and put in:
SELECT * FROM [YourFirstQuery]
UNION Select ' ',null,null,null,null,null From [YourFirstQuery]
ORDER BY [PrimaryKey];

Save it. Name it.
Notice the Select statement after Union. The number of fields must match up. So the first field is blank (' ') then a comma then null, etc.

Use this to create your combobox selecting the third option from the wizard.
Also use this as the recordsouce of your form.
Now when the form opens, it'll look blank.
So the Union query just added a blank record to your query output.

 

[aclambert]

That sounds like a plan, I'll give that a go!

Thanks for all your help.

Cheers

Aaron

 

[PHV]

use this as the recordsouce of your form
And now the form becomes not updatable.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Well, I could say that the post says "displays customer account records from a query". So just displays, no updating.
But forgot to test that. So aclambert, does it need to be updatable or just displayable?

 

[aclambert]

The form I have created has the combined function of viewing account records and amending them, so it does need to be possible to update these fields.

Given this response I think that still makes your original post correct, doesn't it? I haven't tried it just yet, I'm looking at something else but hopefully will have implemented it tonight or tomorrow and will post back my findings from there.

Thanks
Aaron

 

[fneily]

Ok. You want to open to blank fields. Set the form's Data Entry property to Yes.

Create your command button, using option three.
Go into the On Click event of the command button and add some lines to the code as such:
Dim rs As Object
Me.DataEntry = False
Me.RecordSource = "Select * from [YourQueryName]"
Set rs = Me.Recordset.Clone
rs.FindFirst "[TID] = " & Str(Me![Combo14])
Me.Bookmark = rs.Bookmark

As you'll add
Me.DataEntry = False
Me.RecordSource = "Select * from [YourQueryName]"

You can thank PHV for making me think again.

 

[aclambert]

Thanks fneily

Just to make sure I'm getting this right, does this go in the On Click Event for the button which actually launches my form or does it go on the combo box which would have me select a record to populate the fields on this form?

Also, I've tried this on the combo box and it didn't like the TID part of the code. Is this something I need to replace? I did change the YourQueryName element though!

Thanks
Aaron

 

[fneily]

Oops. I typed command button. Sorry. Yeah, it's on the combobox.
Your form will be launched with DataEnty set to Yes.
TID is just a field I was playing with. You'd change it to your own primary key, which I hope you have in the query. Make sure your query is updatable. Try to change values in the queries output. Also at the bottom of your screen it will tell you it's not updatable.
Also change the combobox name.

 

[aclambert]

Thought I would let you all know that I've implemented this across a few forms in my database now and it has worked in all scenarios and does exactly what I want.

Thanks to all of you who had an input, you've been very generous with your advice!

cheers

Aaron