Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Posts
    102

    Unanswered: Adding a record.

    When adding a record in a table through the use of a form. A user only needs to fill in data for the required field(s) for a record to be created. Example: for a table called Invoice, the Invoice Number field is required. When a user key in data in a form for that table, the user must supply/enter the invoice number in order to create a new record, otherwise access will give a null value error. But when a invoice number is typed in to the required field, Access creates the record without some type of confirmation. You can then go back to the table and see that the record exist even if only one field (Invoice Number) of the record has data and the rest is empty.

    What I want the form to do is, once the user finish typing in the invoice number, the form should not automatically save the record in the table rather a button called Add Record must be pressed inorder to add the record.
    How do I do this?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Personally, I like using unbound forms and controlling the data read/write through recordsets in code when creating new records and generally when editing as well. I try to only use bound forms for viewing/searching data.

    Less work (and overhead) than that, however, is by using an unbound control or an inputbox to get the invoice number so you can validate it first. Either way, you'll need a little code.

    PS: you could fiddle with the "validation rule" but this often proves tricky, unpredictable and still creates a new record. If the validation rule is not satisfied and the user cancels the transaction, the new record is not saved, but the autonumber key that was assigned to it becomes consumed (meaning it is gone).

    For the inputbox, add a button to create a new record (use the form wizard so you get all the prefab code to create a new record). In that code, create a variable and use the inputbox command to request an invoice number from the user (assigned to the varibale). Check the variable for validity (if the user presses cancel or leaves it blank, the variable will be an "empty string", so you can catch it). If the variable contains a valid invoice number, create the new record and apply to variable to the invoice field.

    For the unbound control, the method is very similar, except you are using the unbound control instead of an inputbox to get the data. In this case, put an after update event on the unbound control that validates the data and if it's valid, create a new record and make the bound invoice number field equal the unbound control. In fact, make the bound invoice number field not visible.

    If the form is entirely unbound, you add a "submit" button that has code to validate any field requiring validation. Examples would be: making sure a start date comes before an end date, making sure a due date is at least the current date plus a lead time, price/cost/quantity checks, etc. Anything not correct gets a message box and sets the focus to the errant field. Once all is clear, a recordset opens, a new record is created and all the data written. If you make a field on your form for each field in your table (hide the ones you don't want the user to see) you can use a nifty little loop to write the data to the table that won't require editing if you add or remove fields from the table.

    Have fun!

    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    What I want the form to do is, once the user finish typing in the invoice number, the form should not automatically save the record in the table rather a button called Add Record must be pressed inorder to add the record.
    Of course, I now read the rest of your post. Make the invoice field hidden and add an unbound field. Instead of using the afterupdate event I talked about above, use a command button to do the same thing.

    You'll have to make it so the user can not add anything else until a valid invoice number is entered and a new record created since you have a bound form.

    tc

  4. #4
    Join Date
    Jun 2005
    Posts
    102
    Quote Originally Posted by tcace
    Of course, I now read the rest of your post. Make the invoice field hidden and add an unbound field. Instead of using the afterupdate event I talked about above, use a command button to do the same thing.

    You'll have to make it so the user can not add anything else until a valid invoice number is entered and a new record created since you have a bound form.

    tc
    Hi Trace, thanx for your responses and for helping. Could you provide me a sample of the code, because I don't know VB very well.

  5. #5
    Join Date
    Jun 2005
    Posts
    102
    How does the code looks like in VB for adding a record?

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    A quick and dirty way to get some code is to add a command button using the wizard.

    For adding a new record, that code looks like so:
    Code:
    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click
    
    
        DoCmd.GoToRecord , , acNewRec
    
    Exit_Command0_Click:
        Exit Sub
    
    Err_Command0_Click:
        MsgBox Err.Description
        Resume Exit_Command0_Click
        
    End Sub
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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