Button Does More Than One Process

[mattcdse]

Hi all,

I need a button on a form to not only add a new record but also run a query. I can't see how to do this using a macro and my coding experience is limited. How can I get this button to do both of these actions?

I think that I should add that the add record action must happed first. However, I have noticed that this automatically increments the record number in the navigation section to the next number and blanks the firelds in the form (which is data entry mode). As the query relies on information given in the fields on the form, i don't want the field to be blanked. I'd like the the for to stay on the same record until the query has finished. Once this has happened, I'd like the form to reset itself so that it looks as if it's just been opened and the first record is the new record (just as if you'd opened a form in data entry mode).

Sorry, complex I know. I'm sure there's some one out there how's got the answer though.

Cheers,

Matt

 

[ansentry]

If I understand you correctly, you have a form set to data entry = Yes.

You want the user to enter all the data into the form, have the data saved to the source next then run a query and then go to a new record.


If I am correct, you will need behind your command button

'Code to save the current record.
DoCmd.RunCommand acCmdSaveRecord

'Code to Turn of the warning re runing a query.
DoCmd.SetWarnings False

'Code to run the query.
DoCmd.OpenQuery "[COLOR="Blue"][B]NameOfYourQuery[/B][/COLOR]"

'Code to Turn the warnings back on.
DoCmd.SetWarnings True

'Code to  go to the next record.
DoCmd.GoToRecord , , acNewRec

Make sure that you enter your query name in lieu of "NameOfYourQuery"

I have not test this, so if you have a problem post back.
When you have this working you should look at testing the form to see if it has data in the required controls before you run the code.You don't want to save a record with an auto number and thats all.

Good Luck.

 

[mattcdse]

Nearly perfect!

Thanks John,

That code works a treat. It was much easier to do than I expected. The only bit I have trouble with is the acNewRec command. If, say, I've just entered in the first record onto the form and pressed the button, when the acNewRec command runs, it leaves the newly created record available in form as record one and now the new record is record two.

What I would like is the new record to always be record one and any record that have been entered using the form while it was open to not be available.

Cheers,

Matt

 

[ansentry]

Matt,

As I said;

I have not test this, so if you have a problem post back.

Should read I have not tested.

I will create a sample for myself and give the code a try.

Will get back within 24 hours.

 

[ansentry]

Matt,

Change this;

'Code to  go to the next record.
DoCmd.GoToRecord , , acNewRec

To This;

'Code to  go to the next record.
Me.Requery

I have test all three (Save, Run Query & Requery) items and they all work fine.

Good luck with your project.

 

[mattcdse]

Nice one!

Thanks John,

Good skills lad, that's worked a treat. Thankyou very much.

Cheers,

Matt