?
Solved

Open form in MS Access that does not get next autonumber

Posted on 2003-03-05
18
Medium Priority
?
532 Views
Last Modified: 2012-06-27
I have a MS Access 2000 Database. I have an autonumber field called Record ID (Primary Key).  I have a form built from a querey in the database called Records.  Can a form be created that does not immediately access the table from which the form was created and assign a new autonumber.  I have thought of possibly splitting the database, but do not have much experience with this aspect.  I am not sure if this would suffice.  I am trying to keep a multi user environment from accessing the form, starting to fill it out, and changing their mind and closing the record.  At this point, the autonumber is already assigned and I need to keep the records in true sequential order.

Points for this problem will increase if the solution is clear and works.

0
Comment
Question by:aldozier
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 1

Expert Comment

by:ComputerWiz
ID: 8073170

Several things that you can do:

1- Make mandatory "required" fields on the table.  If they exit the form w/o entering one of the required fields on the table, the users will get an error like "cannot have null values" "this record will not be saved"

2- Write data validation code to check for null values on the form on the OnClose event.  If there are null values let the user know and close the form w/o saving the records.  Enhance the code to allow the user to enter the missing data.

Here is a bit of code that you can use on the OnClose event

Private Sub Form_Close()

    If IsNull(Me.txtDate) Then
        Beep
        MsgBox "Missing Date", vbCritical, "TCMI"
        docmd.close
    end if
End Sub

0
 

Author Comment

by:aldozier
ID: 8073456
I understand what you are saying computerwiz, but one thing comes to mind.  Since this is a multi-user db, the problem still exists.  If user x opens the form and gets autonumber 11 and user y (in another office) opens form while user x is filling out the form, user y will get autonumber 12.  User x changes mind and doesn't save form. Then you have a gap in numbering.  Autonumber 10 was added 11 not saved a 12 added.  Then you autonumber field will look like eg....5,6,7,.....10,12.
0
 
LVL 1

Expert Comment

by:ComputerWiz
ID: 8073619
k, if that's a concern, then do not link the form to the table or query.  It will be an unbound form.

Create a "SAVE" button that executes a sql statement that inserts the record into the table.

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    Dim sSQL As String

    sSQL = "INSERT INTO tbl_employees ( Last, First, Social) SELECT "
    sSQL = sSQL + Forms!frm_Input.txtLast & ","
    sSQL = sSQL + Forms!frm_Input.txtFirst & ","
    sSQL = sSQL + Forms!frm_Input.txtSocial & "';"
   
    DoCmd.RunSQL (sSQL)
   
   
Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
   
End Sub
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:aldozier
ID: 8074107
Computerwiz, is there a way to change a bound form, created from a query to an unbound form?...or do I have to create the unbound form from scratch??
0
 
LVL 1

Expert Comment

by:ComputerWiz
ID: 8074166

I suppose you can remove the reference from the "Record Source" property of the form.  You'll have to make sure to remove the record source from each control field.

0
 

Author Comment

by:aldozier
ID: 8074278
Computerwiz, I created a copy of my form, and have unbound all the bound fields.  I am now going to work on the SQL Statement to post the data to the table.  I will let you know.
0
 

Author Comment

by:aldozier
ID: 8074289
Is there another way besides SQL Statement to accomplish the population?  
0
 
LVL 1

Accepted Solution

by:
ComputerWiz earned 160 total points
ID: 8074338

there are many ways to do that, this is just one of them... I suppose you could have a temporary table where all input is entered then you run some checks on the table for null records before appending to your production table.


0
 

Expert Comment

by:TinLemon
ID: 8074378
What I would probably do is take off the autonumber of Record ID.  Then, and this is where it might be tricky, read the last Record ID and then increase that number by 1.  In my opinion it would be best to do this once the "save" button was pressed.  

The logic would go something like this:
-Record ID is set as inactive
-User fills out all of form
-User presses save button
-Save button initiates a RecordID process which retrieves last record ID, adds 1 to it
-New RecordID value is set to equal the value of the RecordID field on the FORM(which is probably tied to the table already)  This sets your new record ID for this record.
-Then the completed form is saved a record.

I'll try physically work this out later.

0
 

Expert Comment

by:TinLemon
ID: 8074387
What I would probably do is take off the autonumber of Record ID.  Then, and this is where it might be tricky, read the last Record ID and then increase that number by 1.  In my opinion it would be best to do this once the "save" button was pressed.  

The logic would go something like this:
-Record ID is set as inactive
-User fills out all of form
-User presses save button
-Save button initiates a RecordID process which retrieves last record ID, adds 1 to it
-New RecordID value is set to equal the value of the RecordID field on the FORM(which is probably tied to the table already)  This sets your new record ID for this record.
-Then the completed form is saved a record.

I'll try physically work this out later.

0
 

Author Comment

by:aldozier
ID: 8074425
I am going to try and tackle the SQL statement and see what happens.  I will let you know the outcome.
0
 

Expert Comment

by:geraldgerald
ID: 8074455
Another option is to keep the form bounded. However you will have an attachment to a global database's table with a field that happens to be autonumbered and a userid (i.e. MyNumber and UserID). The premise is that when you save your data (i.e. BeforeUpdate event) you add a record to this table (insert a userid so that users don't pick up numbers generated for other users) then ask for the contents of it immediately after. Then you can add this value to the form's text box connected to the data source you are bound to. Then get rid of the record to keep table size small.

'Put in BeforeUpdate
   If Me.NewRecord Then
      Me.txtEID = CreateEID()
   End If

' Put in AfterUpdate
   ReleaseEID

Public Function CreateEID() As String
' Pop the tblGnum so it creates a new record and stuff your ID into it so no one else
' can use it. Now we can use it as the retrieval filter and the delete filter and it
' will ongoing basis clean it's own self! Wow!
' Primarily used by BeforeUpdate event in frmEvals*
   DoCmd.SetWarnings False
   DoCmd.RunSQL "INSERT INTO tblGnum ([User]) VALUES ('" & Evaluator & "')"
   DoCmd.SetWarnings True
   DoEvents
   CreateEID = DLookup("[EID]", "tblGnum", "[User]='" & Evaluator & "'")
   DoEvents
End Function

Public Function ReleaseEID() As String
' Remove the EID record that was created in CreateEID() now that it has been stored so
' that the gnum table in the corporate database stays small and quick! (Self cleaning)
' Primarily used by AfterUpdate event in frmEvals*
   DoCmd.SetWarnings False
   DoCmd.RunSQL "DELETE * FROM tblGnum WHERE [User]='" & Evaluator & "'"
   DoCmd.SetWarnings True
End Function

If you have problems with Access locking down the entire table (with the autonumber), you will have to coordinate that (retry). I have not had problems yet.
0
 

Expert Comment

by:TinLemon
ID: 8074546
Ok... I got it working perfectly...

I made a database with a numeric field called RecordID.  I inserted a few records with test data, and put number 1 in the first record ID spot, #2 in the second record ID spot.  I then created a button on my Access form, which in turned popped open a button wizard.  I used the standard SAVE RECORD procedure it offers through this wizard.  Then, I went and greated a GetRecord procedure on my own to assist in the creation of the new ID.

Logic:
-Saves partially completed record (no ID yet)
-initiated GetRecord
-goes back to previous record, puts its recordID into a holder
-adds 1 to holder value and puts into new variable (NewID)
-goes forward to last record created (the one without the ID) and inserts value from NewID into RecordID field
-resaves COMPLETE record.
-done.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Compare Database
Dim holder As Integer
Dim newID As Integer

~~~~~~~~
Private Sub GetRecord()

DoCmd.GoToRecord , , acPrevious
holder = RecordID
newID = holder + 1
DoCmd.GoToRecord , , acNext
RecordID = newID

End Sub
~~~~~~~~~~~~~~~
Private Sub SaveButton_Click()

On Error GoTo Err_Command3_Click

    'first save
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'initiates get record procedure
    GetRecord
    'second save
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command3_Click:
    Exit Sub

Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click
   
End Sub

Let me know if you have questions, or want this sample database I created.
0
 

Author Comment

by:aldozier
ID: 8075239
Computerwiz,  I get errors when I try and execute the sSQL script at the DoCmd.RunSQL (sSQL).  Any ideas?  It says I have a syntax in INSET INTO Statement.
0
 

Author Comment

by:aldozier
ID: 8075394
Computerwiz,  cancel the last note.  
0
 
LVL 1

Expert Comment

by:ComputerWiz
ID: 8075845

the quotation marks can give you a little trouble  " ' "

there is a single (') quote in the middle of the (") quotes

0
 

Author Comment

by:aldozier
ID: 8081137
Computerwiz, I did not use you solution direcly.  I used a mix of your thoughts and ideas as well as my own to acieve the desired results.  I incresed the point to 40...enjoy.

TinLemon, I appreciate you ideas as well.  
0
 

Author Comment

by:aldozier
ID: 8081160
Lots of good ideas from the Computerwiz, a truly knowledgable source.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
What we learned in Webroot's webinar on multi-vector protection.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question