Command Button Usage Only

[dylcon]

Hello, I have a userform that simply adds records to the database. My concern is for a user that is not familiar with Access. They may not realize that 'Tab' is how it is entered. I could display a msgbox confirming the adding of the record, but I would rather just have a command button that adds the record to the table. I was wondering how I could make the commandbutton the ONLY way to insert a record.

[JoeM]

Here is one way.

Have your userform bound to a temporary table (which is just a blank copy of your actual table). So as they are entering data, they are writing to this temp table.
Then, have your command button do an Append Query, copying the record over from the temp table to the final table.
You would want to include code clear out the temp table after every add or whenever they click new add (in case they start a record, stop, and then start another new record before clicking your command button).

[dylcon]

That is a really good idea, thank you. Any idea how to write the code to copy it over? I have hardly any experience in VBA on Access so I am pretty poor at writing the code at the moment.

[JoeM]

You don't really need to know any code to copy it over.
Just create an Append Query, that writes the records from your temporary table to your permanent table. Then, just have the command button run that Append Query.
If you use the Wizard to create your Command Button, there is an option under Miscellaneous for "Run Query".

The only VBA code you really need to know is to delete records out of your temporary table after processing. That code will look something like this:

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [TempTableName].* From [TempTableName];"
DoCmd.SetWarnings True

The first line just suppresses the warning message that you are about to delete records, and the last line turns your warnings back on.

[dylcon]

Ok thank you very much. I put that on the event AfterUpdate of the form. Will that suffice?
I was going to put it on the commandbutton as well, but I do not know how to add code when the query is already being executed.

[JoeM]

Ok thank you very much. I put that on the event AfterUpdate of the form. Will that suffice?

I am not sure the timing of that will be right. I think you want to put it in the command button.

You can can create a macro to do all the steps you want. Create a new Macro, add the following 4 actions:
- OpenQuery: select the Append Query name
- SetWarnings: set to No
- RunSQL: enter the SQL code from my last post above (starting with "SELECT..." but with no quotes).
- SetWarnings: set to Yes

Save the Macro.
Now just have your Command Button run this Macro. You could also convert the Macro to Visual Basic (their is a utility on the Macro menu that does that), and attach the VBA code to your button.

As a precaution, you may also want to add the "Delete" code to the On Load and the On Close events.

[dylcon]

Where is this SELECT statement you are talking about?

[JoeM]

Sorry, I meant to say "DELETE", not "SELECT", i.e.

Delete [TempTableName].* From [TempTableName];

[dylcon]

Hmmm it is not updating the information to the permanent table. Any idea why this is not working?

[JoeM]

Temporarily comment out the DELETE line, and then try again.
If it does not add the record, trying running your Append Query ("TempCustQuery") manually.
Does it work or does that give you some sort of error?
You may have something like a key field violation (or some other error) that is not allowing you to Append the record.

[dylcon]

It is working fine when I run the Query manually, but when I click the command button, it displays the msgbox, but still does not update the tblCustomer for some reason.

Private Sub Command18_Click()    DoCmd.SetWarnings False
    DoCmd.OpenQuery "TempCustQuery", acViewNormal, acEdit
    'DoCmd.RunSQL "Delete [tblTemporaryCustomer].* From [tblTemporaryCustomer];"
    DoCmd.SetWarnings True
    MsgBox (Me.TempCustName & " has been entered as a new company.")


End Sub

[JoeM]

I think we just need to refresh the Form so that the record is added to the Temp table.

Try this:

Private Sub Command18_Click()    
    Me.Refresh
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "TempCustQuery", acViewNormal, acEdit
    'DoCmd.RunSQL "Delete [tblTemporaryCustomer].* From [tblTemporaryCustomer];"
    DoCmd.SetWarnings True
    MsgBox (Me.TempCustName & " has been entered as a new company.")
End Sub

[dylcon]

So I am guessing that the .OpenQuery is just opening and not running the query so I copied that SQL from the query and now am intending to use:

    DoCmd.RunSQL "INSERT INTO tblCustomer ( CustomerName, CustomerNotes )" SELECT tblTemporaryCustomer.TempCustName, tblTemporaryCustomer.TempCustNotes 
FROM tblTemporaryCustomer;"

but that is coming up as incorrect syntax. How do I enter this correctly as I am pretty sure this will solve the problem.

[JoeM]

That is not necessary. Opening any Action query will run it.
If you make the change I listed in my previous post, it should work.
I recreated your scenario and tested it out myself and confirmed that it works when that new line is added.

[dylcon]

Joe, my previous post was done before I saw your refreshing statement. That cleared up the problem. Thank you.

Any chance you know how to make the textboxes of the form blank once the command button is entered as well?