Save form data and clear fields

[jeds]

I have built a form to enter new members and add them to the members table. The record source for the form is a query that pulls the fields from the members table (name, address etc.) that I need for each new member, where the first field value = null:

AddNewMemberQ:

[COLOR=#1f497d][COLOR=#1f497d]SELECT Members.LastName, Members.FirstName, Members.Address, Members.City, Members.State, Members.ZIP, Members.MemberID, Members.Phone, Members.CellPhone, Members.SignInSheetPosition[/COLOR]
[COLOR=#1f497d]FROM Members[/COLOR]
[COLOR=#1f497d]WHERE (((Members.LastName) Is Null));[/COLOR]
[/COLOR]

(MemberID is excluded from the form, it is the unique index for each member)

My problem is on the form. I have a command button to save the entered data and it needs to clear the form so if there is another new member to enter the user can do so.

Right now I have tried many different things to get the command button to both save the data and clear the fields. Depending on what I have tried, the entered data gets saved to the member table either after closing and opening the form, or after clicking “refresh” on the Access ribbon, which will not be available after I split the database to protect the backend from inadvertent end-user changes.

I do know that after clicking the button the data is still “dirty” indicated by the pencil icon in Access. A previous issue, also with a form command button, I just had to start the sub routine with:

[COLOR=#1f497d][COLOR=#1f497d]DoCmd.RunCommand acCmdRefresh[/COLOR]
[/COLOR]

I try to keep track of all the possible combinations that I try, and the results. But I am not a programmer or a coder, and I eventually get lost in all the possible combinations. Some of what I have tried includes:

[COLOR=#1f497d][COLOR=#1f497d]Private Sub SaveNewMember_Click()[/COLOR]
 
[COLOR=#1f497d]If Form.Dirty Then Form.Dirty = False[/COLOR]
 
[COLOR=#1f497d]If Not Form.NewRecord Then Form.NewRecord  -  (I think the VB editor complained about this one, tried it anyhow)[/COLOR]
[COLOR=#1f497d]End If[/COLOR]
 
[COLOR=#1f497d]End Sub[/COLOR]
[/COLOR]

----------------------------------
saves record if form is closed:

[COLOR=#1f497d][COLOR=#1f497d]DoCmd.RunCommand acCmdSaveRecord[/COLOR]
[COLOR=#1f497d]   DoCmd.Close[/COLOR]
[COLOR=#1f497d]   Form.RecordSource = "AddNewMemberQ"[/COLOR]
[COLOR=#1f497d]   Me.Requery[/COLOR]
[COLOR=#1f497d]   Me.Refresh[/COLOR]
 
[/COLOR]

--------------------------------------
saves record, doesnt clear form:

[COLOR=#1f497d][COLOR=#1f497d]DoCmd.RunCommand acCmdSaveRecord[/COLOR]
[COLOR=#1f497d]DoCmd.GoToRecord , , acNewRec[/COLOR]
[/COLOR]

---------------------------------------
works if close AddNewMember (form) and hit refresh in the ribbon:

[COLOR=#1f497d][COLOR=#1f497d]Private Sub SaveNewMember_Click()[/COLOR]
[COLOR=#1f497d]If Me.Dirty Then Me.Dirty = False[/COLOR]
[COLOR=#1f497d] If Not Me.NewRecord Then[/COLOR]
[COLOR=#1f497d] DoCmd.RunCommand acCmdRecordsGoToNew[/COLOR]
[COLOR=#1f497d]End If[/COLOR]
[COLOR=#1f497d]End Sub[/COLOR]
[/COLOR]

-------------------------------------

[COLOR=#1f497d][COLOR=#1f497d]Private Sub SaveNewMember_Click()[/COLOR]
[COLOR=#1f497d]DoCmd.RunCommand acCmdRefresh[/COLOR]
[COLOR=#1f497d]If Me.Dirty Then Me.Dirty = False[/COLOR]
[COLOR=#1f497d]Me.Requery[/COLOR]
[COLOR=#1f497d]Me.Refresh[/COLOR]
[COLOR=#1f497d]Me.LastName = Null[/COLOR]
 
[COLOR=#1f497d]End Sub[/COLOR]
[/COLOR]

---------------------------------------

[COLOR=#1f497d][COLOR=#1f497d]Private Sub SaveNewMember_Click()[/COLOR]
[COLOR=#1f497d]DoCmd.RunCommand acCmdRefresh[/COLOR]
[COLOR=#1f497d]DoCmd.RunCommand acCmdSaveRecord[/COLOR]
[COLOR=#1f497d]Me.Requery[/COLOR]
[COLOR=#1f497d]Me.Refresh[/COLOR]
[COLOR=#1f497d]Me.LastName = Null[/COLOR]
 
[COLOR=#1f497d]End Sub[/COLOR]
 
[/COLOR]

------------------------
Hope I got the duplicates out…I do realize some of the above examples are without the sub routine start and end statements, for clarification.
Some of the things I have tried come from several searches on this forum.

 

[Pat Hartman]

If you set the form's DataEntry property to Yes, it should work the way you want without code. All the user needs to do is press the New record button on the navigation bar. Access automatically saves the current record and scrolls to a new record. No code required.

 

[jeds]

Thanks Pat. It's almost working except for 3 things:

1st, the navigation bar will not be in the end user interface. It is not obvious enough to not make it evident that they need to go down there in order to go to the next new record, and we can place command buttons on the form to make it obvious if not automatic.

Secondly I placed a command button on the form using the wizard "Category: Operations"; " Action: Add New Record" which does save the data and opens the next new record. The issue is that a message box pops up "You can't go to the specified record". If the user clicks "OK, then he is on the next new record. So Access is wrong, you can go to the specified record.

Do I just change the message box message from =[MacroError].[Description] to something like "Success"?

Finally, as I explained in my post

The record source for the form is a query that pulls the fields from the members table (name, address etc.) that I need for each new member, where the first field value = null

.

With this new command button the query is actually opening, does that happen after splitting the database? I ask because I thought the purpose of splitting the database is to protect the backend (tables and queries) from inadvertent changes by the end-user.

Thanks again.

jeds

 

[jeds]

Update: Save form data and clear fields

At this point I have my form working by working around the error message (changed the message to "success").

And to close the query I have added a close window command to the macro for the command button.

Unless someone can tell me I am creating problems for myself down the road I have to consider the problem solved and keep moving down the road.

Thanks for the help

jeds