Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unbound form to input new record (2000)

    Just checking something - is creating an unbound form to input a new record as simple as using the form wizard, and then removing the table from the control source?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Unbound form to input new record (2000)

    You must clear the Record Source of the form as a whole, and the Control Source of all bound controls.

    Plus, you must create code to save the data entered by the user, for example in the On Click event procedure of an 'OK' or 'Save' button. Since the form is now unbound, the data won't be saved automatically.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    Right-o. I'm assuming that means I'll have to use code something along the lines of:

    rst.Open "tblPersonnel", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!Individual = [name]
    rst!COMPANY = [COMPANY]
    and so on and so on?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Unbound form to input new record (2000)

    Yes. Don't forget
    rst.Update
    rst.Close
    at the end.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    Wow...I have some very monotonous typing to do (over 50 fields). Now since I've got multiple tables this data needs to get inputted into, how do I specify which table it's going to? The PK is SSN, and for example,

    tblPersonnel
    SSN
    LName
    FName
    MI

    tlbContact
    SSN
    HomeAddress
    HomePhone

    Do I just repeat the code specifying a different table before End Sub?

    I planned on using rst.Close like this:

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Unbound form to input new record (2000)

    For each table you want to add a record to, you must have code of the form

    rst.Open "NameOfTheTable", ...
    rst.AddNew
    rst!Field1 = ...
    rst!Field2 = ...
    rst.Update
    rst.Close

    (you MUST close the recordset for each individual table, not just at the end of the code)

    If TableB has a foreign key that is linked to the primary key in TableA, make sure that you create and update the record in TableA before you create the record in TableB.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    I don't know what the majority of us would do without you. Thanks Hans.
    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    Jeremy

    Check out the attached demo from the Manxman's MS Access Pages for working example

    HTH

    John

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    John, thanks for the example. I *hope* I can take it from here [img]/forums/images/smilies/smile.gif[/img]
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    I've followed the steps, done the work, checked it twice, and now I get this error (vbExclamation)
    __________________________________________________ ___________________________________________
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    __________________________________________________ ___________________________________________

    Here's the code I have associated with the command button (cmdAddNew), as well as the form itself:

    _______________________________
    'Adds record from unbound form to three separate tables

    Private Sub cmdAddNew_Click()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection

    rst.Open "tblPersonnel", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!Ssn = txtSSN
    rst!COMPANY = cboCOMPANY
    rst!GRADE = cboGRADE
    rst!LOCATION = cboLOCATION
    rst!RANK = cboRANK
    rst!GLASSES = chkGLASSES
    rst!ALLERGIES = txtALLERGIES
    rst!BLD_TYP = txtBLD_TYP
    rst!DCTB = txtDCTB
    rst!DOB = txtDOB
    rst!DOR = txtDOR
    rst!EAS = txtEAS
    rst!EYE_CLR = txtEYE_CLR
    rst!FNAME = txtFNAME
    rst!HAIR_CLR = txtHAIR_CLR
    rst!HT = txtHT
    rst!LNAME = txtLNAME
    rst!MEAL_CARD = txtMEAL_CARD
    rst!MI = txtMI
    rst!MOS = txtMOS
    rst!PEBD = txtPEBD
    rst!RELIGION = txtRELIGION
    rst!SEX = txtSEX
    rst!SVC = txtSVC
    rst!WORK_SEC = txtWORK_SEC
    rst!WT = txtWT
    rst.Update
    rst.Close


    rst.Open "tblAddresses", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!Ssn = txtSSN
    rst!CELL_PHONE = txtCELL_PHONE
    rst!FATHER_ADDRESS = txtFATHER_ADDRESS
    rst!FATHER_CITY_STATE_ZIP = txtFATHER_CITY_STATE_ZIP
    rst!FATHER_NAME = txtFATHER_NAME
    rst!FATHER_PHONE = txtFATHER_PHONE
    rst!HOME_ADDRESS = txtHOME_ADDRESS
    rst!HOME_CITY_STATE_ZIP = txtHOME_CITY_STATE_ZIP
    rst!HOME_PHONE = txtHOME_PHONE
    rst!MARRIED = txtMARRIED
    rst!MOTHER_ADDRESS = txtMOTHER_ADDRESS
    rst!MOTHER_CITY_STATE_ZIP = txtMOTHER_CITY_STATE_ZIP
    rst!MOTHER_NAME = txtMOTHER_NAME
    rst!MOTHER_PHONE = txtMOTHER_PHONE
    rst!NOK_ADDRESS = txtNOK_ADDRESS
    rst!NOK_CITY_STATE_ZIP = txtNOK_CITY_STATE_ZIP
    rst!NOK_NAME = txtNOK_NAME
    rst!NOK_PHONE = txtNOK_PHONE
    rst!NOK_RELATION = txtNOK_RELATION
    rst!NUMBER_OF_DEPENDENTS = txtNUMBER_OF_DEPENDENTS
    rst!SPOUSE_ADDRESS = txtSPOUSE_ADDRESS
    rst!SPOUSE_CITY_STATE_ZIP = txtSPOUSE_CITY_STATE_ZIP
    rst!SPOUSE_NAME = txtSPOUSE_NAME
    rst!SPOUSE_PHONE = txtSPOUSE_PHONE
    rst!WORK_PHONE = txtWORK_PHONE
    rst.Update
    rst.Close


    rst.Open "tblPersonalInfo", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!Ssn = txtSSN
    rst!HMMWV_LICENSE = chkHMMWV_LICENSE
    rst!BOOT = txtBOOT
    rst!CLEARANCE_DATE = txtCLEARANCE_DATE
    rst!FLAK = txtFLAK
    rst!GAS_MASK = txtGAS_MASK
    rst!GORETEX_BOTTOM = txtGORETEX_BOTTOM
    rst!GORETEX_GLOVE = txtGORETEX_GLOVE
    rst!GORETEX_TOP = txtGORETEX_TOP
    rst!HELMET = txtHELMET
    rst!JACKET = txtJACKET
    rst!SAPI = txtSAPI
    rst!SECURITY_CLEARANCE = txtSECURITY_CLEARANCE
    rst!TROUSER = txtTROUSER
    rst.Update
    rst.Close

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub


    _____________________________
    'Check the SSN field to make sure data is correct
    'If data doesn't meet criteria, go back to SSN field
    'and let the data entry clerk know where they messed up
    Private Sub txtMOS_Enter()
    If Len(Me.txtSSN) < 9 Or Len(Me.txtSSN) > 10 Then
    MsgBox "Enter 10 digit SSN", vbExclamation
    Me.txtSSN.SetFocus
    End If

    End Sub

    _____________________________
    'Takes for granted most common data entry error
    'Adds a zero in front of a 9 digit SSN

    Private Sub txtSSN_AfterUpdate()
    If Len(Me.txtSSN) = 9 Then
    Me.txtSSN = "0" & Me.txtSSN
    End If
    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Unbound form to input new record (2000)

    Temporarily comment out the line On Error Goto ErrHandler (by inserting an apostrophe ' in front of it).
    Which line is highlighted when the error occurs?

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    In the exit handler remove
    rst.close
    as you have already close the rst after the rst.update line

    Did this solve the problem ?
    If not, comment out the line :
    On Error GoTo ErrHandler
    by adding a ' before it and tell us what line generate the error.
    Francois

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    It stops on the line:

    rst!GLASSES = chkGLASSES

    That's a checkbox, and it is in the table, too.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    Put it in a if condition :

    If Not IsNull(chkGLASSES) Then
    rst!GLASSES = chkGLASSES
    End If
    Francois

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound form to input new record (2000)

    Francois, works great. Thanks for your help!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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