Unbound form to input new record (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

It stops on the line:

rst!GLASSES = chkGLASSES

That’s a checkbox, and it is in the table, too.

Reply | Quote

Put it in a if condition :

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

Reply | Quote

Francois, works great. Thanks for your help!

Reply | Quote

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.

Reply | Quote

I don’t know what the majority of us would do without you. Thanks Hans.

Reply | Quote

Jeremy

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

HTH

John

Reply | Quote

John, thanks for the example. I *hope* I can take it from here

Reply | Quote