Adding New Records via a Form (A2K-SR1)

**adrianj** · 2002-08-23 12:31

Hi,

I have a really simple form bound to a table that I want to bulletproof for plant operators to enter data. There are five fields arranged in a column under a heading of "Current" which is where the operator will type in the data, and then next to that I have some unbound, disabled, text boxes that are populated from the last record that was entered under a heading of "Most Recent". I haven't yet, but I will remove the record navigation buttons to stop operators scrolling back and "fiddling" historical data. (There is another read-only form to view historical data.) There are three cmd buttons - "New", "Save" and "Close". New clears the textboxes and then populates the first two with a sequential furnace tap number (1 greater than the most recent one) and today's date respectively. The operator will then fill in the time, temperature and tons tapped from the furnace and click on "Save" to submit the record.

I haven't written the few lines of code to do the save yet, but when I click on the record selector to submit the record, I use the "Before Update" event to check that all the fields are filled in with valid data. If something is missing, I cancel the update event and give an appropriate message and return to the form. If all the data is in order, I want to use the "After Insert" event once the new record has been added to populate the unbound textboxes with the latest info and to move to a new record, but not yet populate the first two fields with anything in case there is no more data to be added at that point. If there is more data, then the operator must click on "New" again and the process is repeated.

The code I tried is :

Private Sub Form_AfterInsert()
PopulateRecent 'Copies the relevant fields from the most recent record to the unbound textboxes
Me.Requery
DoCmd.GoToRecord , , acNewRec
End Sub

I don't know if the "Requery" is necessary, but since I am moving to a new record, I didn't think it would matter whether it was there or not. Now here is the problem - once the above code has finished running, the record selector has the focus and the record indicator is on the last record entered. Stepping through the code, when I execute the "acNewRec" line, the form is exactly as I want it - sitting and waiting on a new blank record. When I execute the final "End Sub", the focus jumps to the record selector and the record counter decreases by one back to the last record. Why can't I get the form to sit and wait on a new blank record for input ?

I tried the above code on the "After Update" event as well to no avail. I haven't tried opening the form in data entry mode yet - I don't know if I could use that feature to get around this hassle?

Any ideas please ? I am sure I am missing something really obvious !

All this because I don't want the operator to use the >* to add a new record <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Thanks,

**MarkLiquorman** · 2002-08-23 12:39

I'd take out the Me.Requeryn in the AfterInsert event. I don't see that it is serving any purpose.

**adrianj** · 2002-08-23 12:44

Thanks - I guessed it was unneccessary.

I have tried it without the requery already, but still the form ends up back on the last record, not the new record . . .

**HansV** · 2002-08-23 13:21

(1) I created a form based on your description, and the AfterInsert event does what you want there. So I suspect that other code is intervening to set the form back to the last record.

(2) Why don't you use an unbound form? The "Save" button can do the data validation, and if everything is OK, write the contents of the data entry text boxes to a new record, copy the contensts to the "previous" text boxes and clear the data entry text boxes. Seems to be less worry...

**Bat17** · 2002-08-23 13:24

Another way that might be worth a look would be to not bind the table to the form at all. Then you would not need code to stop them cheating. You could then use code to append the fields to your table, update your recent fields from the current fields and then clear the current fields ready for more data.

HTH

Peter

**MarkLiquorman** · 2002-08-23 17:37

What is the sequence of events that causes the record to be saved? That is, is there a Save button? Or is it just tabbing off the last control on the screen? What is your form's Cycle property set to? (this is on the Other tab in the Properties box). Do you have any code in your Current event that might cause this?

**patt** · 2002-08-23 23:10

Hi Adrian,
Have you turned off Allow Edits and Allow Deletions?

I think you will have some trouble having a SAVE button, I tried it and it kept telling me that "you cannot save the record at this time", however, when I let the save happen after the last control on the form it worked ok.

In the NEW button I put the following code:
DoCmd.GoToRecord , , acNewRec
FurnaceTapNo = 1
DateTapped = Date
TimeTapped.SetFocus

In the AfterInsert event I put the following code:
TextFurnaceTapNo = FurnaceTapNo.OldValue
TextDateTapped = DateTapped.OldValue
TextTimeTapped = TimeTapped.OldValue
TextTemperature = Temperature.OldValue
TextTonsTapped = TonsTapped.OldValue
DoCmd.GoToRecord , , acNewRec
CommandNew.SetFocus

If you want my sample database I am only too happy to send it.

HTH
Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

**adrianj** · 2002-08-28 07:23

<img src=/S/ranton.gif border=0 alt=ranton width=66 height=37>The joys of my reliable internet connection here in Zambia <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> - first I see a whole host of replies to my post while I have been disconnected and then I lost this reply while previewing it !! That'll teach me not to use notepad to compile my replies. Here we go again . . . another day and a half later <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/rantoff.gif border=0 alt=rantoff width=66 height=37>

Thanks to Mark, Hans, Peter and Pat for your replies.

Hans - my code also worked fine when I used a save button with the following code If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord. The problem arises if I save the record using the record selector on the left hand side of the form. As I said at the time of posting I hadn't yet created the save button, so during testing of the Before Update and After Insert form event code, I was using the record selector to save the record. I think it is something to do with the fact that the record selector retains the focus after the record has been saved. Anyway, the form works - I will just hide the record selector - but I am still curious to know why it behaves strangely when clicking the record selector ?

The unbound form as suggested by yourself and Peter seems a good idea to make sure the operators can't get at any other records. I think I will dig out some sample code and try it out - it's about time I got more familiar with DAO. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> OK - I found some - I have pasted some code (trimmed down) from "Roger's Access Library" at the end - is this the type of code I am going to need if I want to use an unbound form ? One question about the code - if one is opening a recordset from a table, when would one use the dbOpenDynaset type (as used below) rather than the dbOpenTable type ?

Mark - the problem, as already mentioned was when the record was saved using the record selector. Tabbing off the last txtBox control moved the focus onto the cmdButtons. Continuing to tab would eventually tab off the current record and the Before Update and After Insert code worked fine. The Cycle property was set to "All Records". There is no other form event code except for "Open" which populates the unbound textboxes for the first time.

Pat - I hadn't used the Allow Edits or the Allow Deletions properties, but if I continue with a bound form, then I will activate and deactivate them appropriately using the "New" and "Save" buttons. That way I can stop operators from "stealing" tons from previous taps and adding them to theirs ! I think I have taken care of the inability to save records from the "Save" button with the if Me.Dirty . . . statement mentioned above ? As regards the OldValue property - I don't think I will use that as the operators may not necessarily be entering the tap data in chronological order because they may only catch up all the info towards the end of their shift. I have defined an index with no duplicates on the TapNo to make sure they can't duplicate a tap by mistake. I would appreciate it if you could send me a copy of your sample db - thanks.

A final question - what is the code equivalent of pressing "Esc" to cancel the editing of a record ? I know I can cancel an update event, but how do I essentially discard edits (using code) before moving to a new record ? TIA

----------------------------------------------

Code for an unbound form :

Private Sub btnADD_Click()
'**********************************
'Created by Roger Carlson *
'Roger.Carlson@spectrum-health.org*
'Rog3erc@aol.com *
'**********************************

Dim db As Database, rsCust As Recordset, Criteria As String

On Error GoTo ErrorHandler
Set db = CurrentDb
Set rsCust = db.OpenRecordset("Customers", DB_OPEN_DYNASET)

Criteria = "ACCOUNT=" & Val(Me!ACCOUNT)
rsCust.FindFirst Criteria
If rsCust.NoMatch Then

rsCust.AddNew
rsCust("ACCOUNT") = (Me!ACCOUNT)
rsCust("CUSTOMER_AR_NUMBER") = Me!CUSTOMER_AR_NUMBER

' . . . . more statements here

rsCust.Update

Else

MsgBox "Account Number: " & Me!ACCOUNT & " already exists.", 48, "ERROR!"
Me!ACCOUNT.SetFocus

End If

rsCust.Close
db.Close
Me!cboFind.Requery

Exit_btnADD_Click:

Exit Sub

ErrorHandler:

MsgBox Error$
rsCust.Close
db.Close
GoTo Exit_btnADD_Click:

End Sub

----------------------------------------------

Thanks for your help,

Regards,

**HansV** · 2002-08-28 07:58

Hi Adrian,

(1) I don't know why the record selector caused problems. I routinely hide it in all my forms.

(2) To add a new record, you can use two methods:
<UL><LI>Use DAO, as in the code you posted.
<LI>Create the SQL for an append query dynamically and execute it using DoCmd.RunSQL or CurrentDb.Execute.[/list]I mostly use DAO for this.

For a table, I don't think there is much difference between dbOpenTable and dbOpenDynaset. For queries you can't use dbOpenTable.

Note: if you allow some text boxes to remain blank, you must handle it like this:

If Not IsNull([txtFirstName]) Then rstCust![FirstName] = [txtFirstName]

(3) To cancel changes in a control, use

[txtFirstName].Undo

To cancel changes in a record, use

Me.Undo

HTH,
Hans

**adrianj** · 2002-08-28 08:13

Hans - thanks again - it seems your replies are instant. I must owe you at least a case of Grolsch by now <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

As a matter of interest, how did you know I had replied so soon after I posted because I actually posted a reply to Pat's posting although yours was further up the thread. Do you watch each thread where you have posted a reply ?

It looks like the DAO for this job is reasonably straightforward.

Your Undo tips look like just what I am after,

Regards,

**HansV** · 2002-08-28 08:24

Adrian,

I regularly refresh the view in my Control Panel; when new posts/replies appear (numbers in parentheses after the number of threads/posts) I often take a look to see if they interest me. I use a broadband connection, so I can afford to stay online.

<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Hans

Thread: Adding New Records via a Form (A2K-SR1)

Thread Tools