Need to click button twice on form

[VinnieM]

I shall start by apologising, I'm a self teaching newbie and there is most likely something stupid that i've missed, I have two tables, i use one to load my input form then i click a button which is supposed to copy the trade to another table, delete it from the original table, requery the form so that all the fields are blank, ready for the next entry and refresh the second table. Unfortunately, it does work but i have to click the button twice and the second table doesn't show the new record until I close the dB and reopen. Once again I apologise for being so dim.



Private Sub Command156_Click()



On Error GoTo HandleError


Dim dbs As Database

Set dbs = OpenDatabase("Pets2nd")

dbs.Execute " INSERT INTO PetDetails " _
& "SELECT * " _
& "FROM [PetDetailsEntry];"

Dim rst As Recordset

dbs.Execute "DELETE * FROM " _
& "PetDetailsEntry WHERE PetTagNo <> null;"

dbs.Close

MsgBox "Everything posted okay!"

Me.Refresh
Me.Requery


HandleExit:


Exit Sub

HandleError:


MsgBox Err.Description
Resume HandleExit

End Sub

[Bob Fitz]

Why the need for two tables? Why not just enter the data directly into the second table using a bound to the second table?

[VinnieM]

Probably my ignorance, i shall read up on bound.

[VinnieM]

I checked this through and unfortunately it would give the use access to all of the records, in this case that's a big no no, that's why i am doing it the way i am, so the new record is all they have access to, I'm currently trying out Docmd.Openquery to run queries that add the record to the second table, then another that deletes it from the first, this works but again it's refreshing the tables that seems to be an issue so i will try out Docmd.close and Docmd.opentable , messy but it might work.

[CJ_London]

unfortunately it would give the use access to all of the records

only because you are allowing it. Not clear to me what you are trying to achieve, but check out the form allow edits property and the use of criteria in queries.

Suggest you also step through your code and check it is actually doing what it should be doing

[VinnieM]

What i'm trying to achieve is a user enters a record onto a form then that record is then posted to another table, the original record is then deleted from the first table and the tables and form are refreshed so the form is ready to post another record, it's working when i used queries but its the refreshing i need to sort out.

[Welshgasman]

Just set Data Entry to Yes, then all they can do is add records?

[Micron]

I agree that data entry mode is probably the solution. What I don't grasp is why a user can see a record when entering but that user is not allowed to look at it later. If this is about other users not seeing the record, then what do they ever look at? Knowing some of those answers (the why) might produce other suggestions. However, re:

its the refreshing i need to sort out

you're likely to come across that again so I'll attempt to answer for the future as opposed to now.
If you're looking at the table and not seeing it, you should be looking at it in a form instead. The code to commit the record requeries the form where you expect to see it.
Alternatively, use Refresh All from the ribbon and new record should be there in the table datasheet view.

Please use code tags (# button on posting toolbar) to maintain indentation and readability.

[Missinglinq]

Setting Data Entry to Yes/True, on the Form to be used to enter New Records, will accomplish your stated purpose...to allow certain, or all, end users to only enter New Records without the ability to see existing Records. Your approach of using two Tables instead of one is unnecessarily complex...and, I suspect, over time will cause your database to become bloated.

BTW...DataEntry is probably the worst name ever given to a Property! It should have been named 'Only Enter New Records' or something similar.

Linq ;0)>