Full Version: Data Entry Code
UtterAccess Forums > Microsoft® Access > Access Forms
Andrew_Harwin
Hi
oes anyone have any example code to make a command button on a bound form, enter the contents of the form to the table it is bound by?
The form has combo boxes as well as text boxes for dates. Id also like to pop up error messages if any of the fields have been left blank.
Ive tried using some code i used in a database a few years back but ive forgotton how to do it to be honest.
Code is:
Private Sub Cmd_EnterNewReview_Click()
DoCmd.Hourglass True
If Lst_ReviewType <> "" Then
Record_Entered_Msg = "New review record created"
Record_Entered_Msg.BackColor = 16776960
Record_Entered_Msg.SpecialEffect = 2
Cmd_EnterNewReview.Caption = ""
Cmd_EnterNewReview.Transparent = True
Cmd_EnterNewReview.Enabled = True
ViewDetails.Caption = "View details"
ViewDetails.Transparent = False
ViewDetails.Enabled = True
Dim db As Database, rst As Recordset
Set db = CurrentDb()
Dim SQL$
SQL$ = "Select[ReviewStatus],[ReviewStructure],[ReviewType],[ReviewStage],[ReviewStartDa
te],[LogCheckDate],[DesktopDate],[DesktopDate],[CurrentGrade],[GradeDate]FROM Tbl_ReviewDetails;"
Set rst = db.OpenRecordset(SQL$, dbOpenDynaset)
With rst
.AddNew
.Fields("ReviewStatus") = Me.ReviewStatus
.Fields("ReviewStructure") = Me.ReviewStructure
.Fields("ReviewType") = Me.ReviewType
.Fields("ReviewStage") = Me.ReviewStage
.Fields("ReviewStartDate") = Me.ReviewStartDate
.Fields("LogCheckDate") = Me.LogCheckDate
.Fields("DesktopDate") = Me.DesktopDate
.Fields("CurrentGrade") = Me.CurrentGrade
.Fields("GradeDate") = Me.GradeDate
.Update
End With
Set db = Nothing: Set rst = Nothing

Else:
DoCmd.Beep
Record_Entered_Msg = "ERROR! - No Review Type Entered"
Record_Entered_Msg.BackColor = 255
Record_Entered_Msg.SpecialEffect = 1
If IsNull(Lst_Structure) Then
Record_Entered_Msg = "ERROR! - No Structure Entered"
Record_Entered_Msg.BackColor = 255
Record_Entered_Msg.SpecialEffect = 1
If IsNull(Lst_Stage) Then
Record_Entered_Msg = "ERROR! - No Stage Entered"
Record_Entered_Msg.BackColor = 255
Record_Entered_Msg.SpecialEffect = 1
If IsNull(Lst_CurrentGrade) Then
Record_Entered_Msg = "ERROR! - Current Grade Not Entered"
Record_Entered_Msg.BackColor = 255
Record_Entered_Msg.SpecialEffect = 1
If IsNull(Txt_GradeDate) Then
Record_Entered_Msg = "ERROR! - Current Grade Date Not Entered"
Record_Entered_Msg.BackColor = 255
Record_Entered_Msg.SpecialEffect = 1
End If
End If
End If
End If
End If

DoCmd.Hourglass False
End Sub
Any help much appreciated as this is driving me nuts as i should be able to do this.
Cheers
Alan_G
Hi
If the form is bound to the table, you don't need any code to save the record as it is automatically saved to the table when you either close the form or navigate to a new record. You would only need something like you have if you're wanting to save the data to a different table than the current form is bound to.
If you need any more, please post back with a bit more info of what you're trying to do frown.gif
Cheers
Alan
Andrew_Harwin
Hi Alan, thanks for the offer.
There are no fields that are not bound to the table im trying to populate.
The code is falling down with a "Type Mismatch" error code in the line:
Set rst = db.OpenRecordset(SQL$, dbOpenDynaset)
The database ive nicked this code from still works fine. Thats confusing me.
All im trying to do is ge the user to fill in the fields in the form and click the button to enter a new record. But if any fields are left blank i want an error message and for the table not to be populated until the form is fully complete.
Once the form is complete and the table polulated, im sending them to the next form in which to add more detail to the record they just added.
Regards
Andy
Larry Larsen
Hi
Just trying a different approach to some lines of code:
CODE
Dim db As Database
Dim rst  As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("Tbl_ReviewDetails")
With rst
.AddNew
.rst("ReviewStatus") = Me.ReviewStatus
.rst("ReviewStructure") = Me.ReviewStructure
.rst("ReviewType") = Me.ReviewType
.rst("ReviewStage") = Me.ReviewStage
.rst("ReviewStartDate") = Me.ReviewStartDate
.rst("LogCheckDate") = Me.LogCheckDate
.rst("DesktopDate") = Me.DesktopDate
.rst("CurrentGrade") = Me.CurrentGrade
.rst("GradeDate") = Me.GradeDate
.Update
End With
Set db = Nothing
Set rst = Nothing

Not tested..
thumbup.gif
Andrew_Harwin
Larry
tried your suggestion of a different approach but was met with:
Object variable or With block variable not set.
Regarding the line
.AddNew
in code
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("Tbl_ReviewDetails")
With rst
.AddNew
.Fields("ReviewStatus") = Me.ReviewStatus
.Fields("ReviewStructure") = Me.ReviewStructure
.Fields("ReviewType") = Me.ReviewType
.Fields("ReviewStage") = Me.ReviewStage
.Fields("ReviewStartDate") = Me.ReviewStartDate
.Fields("LogCheckDate") = Me.LogCheckDate
.Fields("DesktopDate") = Me.DesktopDate
.Fields("CurrentGrade") = Me.CurrentGrade
.Fields("GradeDate") = Me.GradeDate
.Update
End With
Set db = Nothing
Set rst = Nothing
Larry Larsen
Hi
Sorry try:
rst.AddNew
HTH's
thumbup.gif
Larry Larsen
Hi
Also check your reference libaray for Microsoft DAO 3.6 Object Library is "checked" and in position..
thumbup.gif
Chaga
Hi Larry,
I would also declare the db and recordset as DAO.Database, DAO.Recordset.
Chaga
Also just noticed in Andrew's code this line:
Set rec = db.OpenRecordset("Tbl_ReviewDetails")
should be
Set rst= db.OpenRecordset("Tbl_ReviewDetails")
Cheers
Andrew_Harwin
Still no joy im afraid.
ame error message in same line of code.
Larry Larsen
Hi Charles
Good catch..
Thanks..
thumbup.gif
Andrew_Harwin
Thanks guys.
ollowing Charles input the form now runs without errors.
But only the date fields from the form are populated into the table. These are all text boxes.
Any combo boxes dont go back to the table.
Any ideas?
Andrew_Harwin
Sorry

I was being a fool. Id named the Combo boxes differently so how could it pick them up.

Thanks guys.

Ive got it mostly working. Just need to sort out how to give error messages if a field is incomplete.
Edited by: Andrew_Harwin on Wed Oct 13 9:52:40 EDT 2004.
Chaga
Hi Andrew,
you can put similar code at the beginning of the button's event.
pre> Dim ctl As Control
For Each ctl In Me
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If Len(ctl) = 0 Or IsNull(ctl) Then
MsgBox "This field cannot be blank"
ctl.SetFocus
End If
End If
Next ctl
HTH
Larry Larsen
Hi Andrew
Glad you got it up and running/
Also many thanks Charles for "steering" us back on track..
thumbup.gif
Chaga
No problem wink.gif
Chaga
Andrew, forgot to add an Exit Sub after the ctl.SetFocus.
Andrew_Harwin
How do you blokes know all this?
The code i know have is:
Private Sub Cmd_EnterNewReview_Click()
DoCmd.Hourglass True
Dim ctl As Control
For Each ctl In Me
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
If Len(ctl) = 0 Or IsNull(ctl) Then
MsgBox "All fields must be completed"
ctl.SetFocus
Exit Sub
Else
Record_Entered_Msg = "New review record created"
Record_Entered_Msg.BackColor = 16776960
Record_Entered_Msg.SpecialEffect = 2
Cmd_EnterNewReview.Caption = ""
Cmd_EnterNewReview.Transparent = True
Cmd_EnterNewReview.Enabled = True
ViewDetails.Caption = "View details"
ViewDetails.Transparent = False
ViewDetails.Enabled = True
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Tbl_ReviewDetails")
With rst
rst.AddNew
.Fields("ReviewStatus") = Me.ReviewStatus
.Fields("ReviewStructure") = Me.ReviewStructure
.Fields("ReviewType") = Me.ReviewType
.Fields("ReviewStage") = Me.ReviewStage
.Fields("ReviewStartDate") = Me.ReviewStartDate
.Fields("LogCheckDate") = Me.LogCheckDate
.Fields("DesktopDate") = Me.DesktopDate
.Fields("CurrentGrade") = Me.CurrentGrade
.Fields("GradeDate") = Me.GradeDate
.Update
End With
Set db = Nothing: Set rst = Nothing
End If
End If

Next ctl

DoCmd.Hourglass False
End Sub
And although it comes up with the message "All fields must be completed" it still enters the record regardless and enables the users to go on to the next stage.
What have i missed?
Chaga
See my previous post frown.gif
Andrew_Harwin
Charles
had already seen your post and added the Exit sub where suggested.
Chaga
Remove the Else statement, just put the code I provided (as is, but with the Exit Sub) before the rest.
This will run the routine first, and if there are no blanks or Nulls, will continue to your code.
Andrew_Harwin
Perfect.
Thank you very much.
Andy.
Chaga
No problem, you're welcome.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.