How do I open a form to the record just created?

[Seth Schrock]

I'm using the following vba code to create a new record in one of my tables:

Private Sub btnCreateInvoice_Click()
Dim dbBilling As DAO.Database
    Dim rstInvoices As DAO.Recordset
 
    Set dbBilling = CurrentDb
    Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
 
    rstInvoices.AddNew
    rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
    rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
    rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate
    rstInvoices.Update

The primary key for tblInvoices is an autonumber field titled InvoiceID. After this code is run, I want to open frmInvoices (based on tblInvoices) to the record just created. I believe that I could do this by having the form open to the last record. I just want to make sure that this is a fool proof method. I don't know of a way to get the InvoiceID for the record that was created, otherwise I would just to the record that way. So my idea was to add the following code below the above code:

DoCmd.OpenForm "frmInvoices"
    DoCmd.GoToRecord , , acLast

Is this a good way to do this?

[TheSmileyCoder]

The goto last record is not foul proof, since it would depend on the sorting applied to your recordset. There is a reason for tables to have an ID field, and you should always use that.

I have rewritten parts of your code to show how to use such an id.

    rstInvoices.AddNew 
    rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
    rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
    rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
    rstInvoices.Update 
    Dim lngInvoiceID as long
    lngInvoiceID=rsInvoices!InvoiceID
 
    Docmd.OpenForm "frmInvoices",,,"InvoiceID=" & lngInvoiceID
 

If you have any questions please ask again :)

[Seth Schrock]

I just tried it and it is going to the first record in the table instead of the record just created. I'm lost when it comes to recordset commands so I don't know what is wrong.

[TheSmileyCoder]

A few things to try:

If the form is allready open when you run the docmd.OpenForm, access will not Re-open it, but simply set the focus to the form. This however does not requery the form, and while most recordsets will show changes to existing records, they will not automatically load new records. You will need to requery the form for that.

I dont know what your level of VBA expertise are, but have you debugged to make sure that your variable is passed properly?
For more info on debugging VBA check: Debugging VBA


Im guessing its the first of the two being the issue, allthough its just a guess since I dont have that much information at hand. You can then try:

Forms!frm_Invoice.Requery
Docmd.OpenForm "frmInvoices",,,"InvoiceID=" & lngInvoiceID 

This assumes the form will always be open, when you run your code, if thats not the case you need to add code to check whether the form is open.

[Seth Schrock]

I tried it making sure that the form was closed and that didn't work. On to the debugging... I have read some about it in a book, but never done it for real.

Here is some information about the contents of tblInvoices. There are currently four records with the following PKs: 4, 10, 11, and 12 (due to deleting previous test records). When I debug my code, I set a break point at the

lngInvoiceID=rstInvoices!InvoiceID

line. I then stepped into the line. I then put ? lngInvoiceID in the Imeediate window and it comes back with 4 (the PK for the first record in the table). Here is a copy of the code that I'm running currently:

Private Sub btnCreateInvoice_Click()
Dim dbBilling As DAO.Database
    Dim rstInvoices As DAO.Recordset
 
    Set dbBilling = CurrentDb
    Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
 
    rstInvoices.AddNew
    rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
    rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
    rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate
    rstInvoices.Update
    Dim lngInvoiceID As Long
 
    lngInvoiceID = rstInvoices!InvoiceID
 
    DoCmd.OpenForm "frmInvoices", , , "InvoiceID=" & lngInvoiceID
 
    DoCmd.OpenReport "rptInvoice", acViewPreview
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "uqryAssignInvoiceID"
    DoCmd.SetWarnings True
 
    Me.sfrmInvoices.Requery

[TheSmileyCoder]

Im not really spotting what could be the issue here. Your code to me looks fine.

Is your frm_Invoices based on the table tblInvoices or a query, and if its a query does that query include the InvoiceID field?

What is uqryAssignInvoiceID used for? Please confirm that your InvoiceID is an autonumber as first mentioned.

PS. That would pretty much be the same method of debugging I would start off with for a similar problem.

[Seth Schrock]

Currently, frmInvoices is based on tblInvoices, but I plan on switching it to a query so that I can combine the information from two tables. It will contain the InvoiceID.

InvoiceID is an autonumber. uqryAssignInvoiceID is a long story. Basically, it just assigns an InvoiceID to the services bought so that they are assigned to that Invoice ID. To get the correct Invoice ID, I have the criteria set to find the Invoice ID of the record that has the BeginDate, EndDate, and CustomerID match the values on frmCustomer. This was my way around not knowing how to retrieve the InvoiceID of the record just created.

[TheSmileyCoder]

Sorry, should have been able to spot the issue from #5, but guess I wasn't paying enough attention.

The issue, is that after the recordset has updated, it doesn't stay on the new record, which I thought it did. Therefore:

    rstInvoices.AddNew 
    rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
    rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
    rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
    rstInvoices.Update 
    lngInvoiceID = rstInvoices!InvoiceID 

causes lngInvoiceID to be the ID of the first record (or possibly whichever record the recordset was pointing to when we started the new record).

The solution howerver is simple, we just put the assingment in between the AddNew and update.

    Dim lngInvoiceID As Long 
    rstInvoices.AddNew 
    rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
    rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
    rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
    lngInvoiceID = rstInvoices!InvoiceID    
    rstInvoices.Update 

and then proceed to open the form as previously described.

[Seth Schrock]

That makes perfect sense. I will try it when I get to work tomorrow and let you know the results.

[NeoPa]

Unfortunately the value of rstInvoices!InvoiceID is only set at the point of the call to rstInvoices.Update :-(

May I ask why you are trying to recreate what Access already does for you by creating your record in code? It's generally a recipe for confusion (although there are possible reasons that could make sense in limited circumstances).

I suspect the ID could be retrieved in either situation by navigating to the last record currently available in the existing recordset by the way.

[TheSmileyCoder]

NeoPa Unfortunately the value of rstInvoices!InvoiceID is only set at the point of the call to rstInvoices.Update :-(

Sorry, NeoPa, you are incorrect. Autonumber fields retrieve their value at the point of RecordSet.AddNew or possibly when the first value is entered into any field. Should be easy to test which of the two are true, if one should be interested.

[NeoPa]

You're correct Smiley (of course). I was indeed mistaken and that should work well.

PS. Adding data onto a form in the New Record position is equivalent to the .AddNew method. Two sides of the same coin.

[TheSmileyCoder]

When you start adding data to a forms new record, the Insert event happens, which I believe is equivalant to the .AddNew.

It is actually a common misunderstanding that the Insert event happens when you save a new record, however it happens at the point of first dirtying a new record

[Seth Schrock]

It worked perfectly. Thank-you very much.

[TheSmileyCoder]

Thats nice to hear. :)

[NeoPa]

Smiley:
It is actually a common misunderstanding that the Insert event happens when you save a new record, however it happens at the point of first dirtying a new record

Indeed. I was actually aware of that, having discovered it only quite recently. What I hadn't realised was that the creation of the AutoNumber value was tied to that. It does makes sense I suppose.