By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,941
I'm using the following vba code to create a new record in one of my tables:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCreateInvoice_Click()
  2. Dim dbBilling As DAO.Database
  3.     Dim rstInvoices As DAO.Recordset
  4.  
  5.     Set dbBilling = CurrentDb
  6.     Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
  7.  
  8.     rstInvoices.AddNew
  9.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
  10.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
  11.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate
  12.     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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmInvoices"
  2.     DoCmd.GoToRecord , , acLast
Is this a good way to do this?
Nov 28 '11 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1.     rstInvoices.AddNew 
  2.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
  3.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
  4.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
  5.     rstInvoices.Update 
  6.     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.

Expand|Select|Wrap|Line Numbers
  1.     Dim lngInvoiceID As Long 
  2.     rstInvoices.AddNew 
  3.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
  4.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
  5.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
  6.     lngInvoiceID = rstInvoices!InvoiceID    
  7.     rstInvoices.Update 
and then proceed to open the form as previously described.

Share this Question
Share on Google+
15 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.

Expand|Select|Wrap|Line Numbers
  1.     rstInvoices.AddNew 
  2.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
  3.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
  4.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
  5.     rstInvoices.Update 
  6.     Dim lngInvoiceID as long
  7.     lngInvoiceID=rsInvoices!InvoiceID
  8.  
  9.     Docmd.OpenForm "frmInvoices",,,"InvoiceID=" & lngInvoiceID
  10.  
If you have any questions please ask again :)
Nov 28 '11 #2

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Nov 28 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Forms!frm_Invoice.Requery
  2. 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.
Nov 28 '11 #4

Seth Schrock
Expert 2.5K+
P: 2,941
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
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCreateInvoice_Click()
  2. Dim dbBilling As DAO.Database
  3.     Dim rstInvoices As DAO.Recordset
  4.  
  5.     Set dbBilling = CurrentDb
  6.     Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
  7.  
  8.     rstInvoices.AddNew
  9.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
  10.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
  11.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate
  12.     rstInvoices.Update
  13.     Dim lngInvoiceID As Long
  14.  
  15.     lngInvoiceID = rstInvoices!InvoiceID
  16.  
  17.     DoCmd.OpenForm "frmInvoices", , , "InvoiceID=" & lngInvoiceID
  18.  
  19.     DoCmd.OpenReport "rptInvoice", acViewPreview
  20.     DoCmd.SetWarnings False
  21.     DoCmd.OpenQuery "uqryAssignInvoiceID"
  22.     DoCmd.SetWarnings True
  23.  
  24.     Me.sfrmInvoices.Requery
Nov 28 '11 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Nov 28 '11 #6

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Nov 28 '11 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1.     rstInvoices.AddNew 
  2.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
  3.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
  4.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
  5.     rstInvoices.Update 
  6.     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.

Expand|Select|Wrap|Line Numbers
  1.     Dim lngInvoiceID As Long 
  2.     rstInvoices.AddNew 
  3.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID 
  4.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate 
  5.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate 
  6.     lngInvoiceID = rstInvoices!InvoiceID    
  7.     rstInvoices.Update 
and then proceed to open the form as previously described.
Nov 28 '11 #8

Seth Schrock
Expert 2.5K+
P: 2,941
That makes perfect sense. I will try it when I get to work tomorrow and let you know the results.
Nov 28 '11 #9

NeoPa
Expert Mod 15k+
P: 31,492
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.
Nov 28 '11 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Nov 28 '11 #11

NeoPa
Expert Mod 15k+
P: 31,492
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.
Nov 28 '11 #12

TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Nov 29 '11 #13

Seth Schrock
Expert 2.5K+
P: 2,941
It worked perfectly. Thank-you very much.
Nov 29 '11 #14

TheSmileyCoder
Expert Mod 100+
P: 2,321
Thats nice to hear. :)
Nov 29 '11 #15

NeoPa
Expert Mod 15k+
P: 31,492
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.
Nov 29 '11 #16

Post your reply

Sign in to post your reply or Sign up for a free account.