Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding New Records via a Form (A2K-SR1)

    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,

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    I'd take out the Me.Requeryn in the AfterInsert event. I don't see that it is serving any purpose.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 . . .

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    (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...

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    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>

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <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 <font color=red>If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord</font color=red>. 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 <font color=448800>Before Update</font color=448800> and <font color=448800>After Insert</font color=448800> 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 <font color=red>dbOpenDynaset</font color=red> type (as used below) rather than the <font color=red>dbOpenTable</font color=red> 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 <font color=448800>Before Update</font color=448800> and <font color=448800>After Insert</font color=448800> code worked fine. The <font color=448800>Cycle</font color=448800> 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 <font color=448800>Allow Edits</font color=448800> or the <font color=448800>Allow Deletions</font color=448800> 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 <font color=red>if Me.Dirty . . . </font color=red> statement mentioned above ? As regards the <font color=448800>OldValue</font color=448800> 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,

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <font color=red>Undo</font color=red> tips look like just what I am after,

    Regards,

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •