Input info then assigned ID

[musclecarlover07]

When the user goes to add a record I don't want the ID (Auto number) to fill in automatically. I want the user to have to type in the all the data and when they hit the add button it then assigns an ID. Sometimes the user may have to exit out of the database before they finish the record. When they do this it creates a record with incomplete data or they accidentally create a blank record. When the data is displayed it uses another field as the criteria and when that field is blank it never shows up and it just sits in there.

 

[namliam]

You can only do this using a so called "Unbound form", you dont bind the table to this form but only have boxes sitting there then using code add it to your table.

A lot more work, but if its required....

Research it a bit and come back with any questions you run into.

 

[musclecarlover07]

I was afraid you were going to say that. I have it set up like that and there is more code to call the info. The form has multiuses. It creates new records as well as editing the data. The unbound works for creating records but editing requires a lot more code to display and update the record. I was hoping there might have been an easier solution.

I don't know how much of a difference this will make. But the back end is all on a SQL server.

 

[namliam]

Well you can try changing your form's source when a user wants to enter data.
This source then would be a temp table that can be bound just as easily, then at the end "submit" the record running an append from your temp table into your perminant table?

Maybe that works for you?

 

[namliam]

Or perhaps another idea, add a "hidden" field to your table that is only filled when the actual save button is clicked. Then simply schedule a delete statement on the database daily to remove anything that isnt filled by the save button ?
Offcourse this "wastes" autonumbers, but they should be meaningless anyways.

 

[musclecarlover07]

Make certain fields required, when the user tries to exit with out the required information the record is undone and not saved.

It I have it bound then the auto number is wasted. Like namliam said its meaningless but If I could go with out wasting autonumbers it would be nice. I mean its not a big deal I was just seeing what options there were. Thanks for the options. I will play around with these ideas and see what I works the best.

 

[thmsjlmnt3953]

Unbound isnt as hard as you may think,

set up your form as youd like it with unbound's or combo's etc then add a button and add this code with your own field names and txtbox or cmbbox names etc.

Dim strSQL As String
strSQL = "INSERT INTO tblError (field2,field2,field3) VALUES(txt1, txt2, cmb1)"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Error Entered Successfully", vbOKOnly, "Sucess"

Hope this helps

 

[thmsjlmnt3953]

if you want to check to see fields are filled before inserting the data to the table then you could look at the following:

Dim strSQL As String
txtEnteredBy.SetFocus
If txt1.Text =  Null Then
MsgBox "Please Enter Entered By", vbOKOnly, "Error"
End
End If
strSQL = "INSERT INTO tblYour_Table (Field1, Field2) VALUES(txt1, txt2)"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Error Entered Successfully", vbOKOnly, "Sucess"
Else
End If

 

[musclecarlover07]

Autonumbers are only required to be unique. You are assigning meaning where there is none. Its this kind of thinking that complicating your design. BTW autonumbers are not even required to be a positive number, they can and will run negative. like -10125 then what?

Im sorry if I was unclear. But If the form is bound and then the user creates a new record and begins to type in the first field the ID field populates the next number in sequence. Then lets say they completely close out of access then that number is assigned and used. But if they didn't finished that record then its in the background not being used. So there will be empty fields and so on. I don't want this to happen at all. I'm trying to keep all the records in sequential order as best as possible. If I can't then that is ok,

In the end I think I will be sticking with the unbound method. That was my original ideal to begin with. All the ideal given were great an I appreciate the help. Thanks