Update form record

[lhtan]

I have two Forms: Legal Masterlist and Employee.

The recordsource for each form is "Legal Masterlist" Table and "Employee"
Table.

When I clicked a button in the "Legal Masterlist" form, it'll open up
another form called "Employee". After filling up the latter, a save button is
clicked to save the EmployeeID into the "Legal Masterlist" Table.

I have an Event Procedure for "onLoad" in the "Employee" form that declares
the database and recordset. There is also an "onClick" event procedure for
the save button in this form. Below is the code:

Private Sub Form_Load()
Dim dbsLegalDB As Database
Dim rstLegalMasterlist As Recordset

Set dbsLegalDB = OpenDatabase("LegalDB.mdb")
Set rstLegalMasterlist = dbsLegalDB.OpenRecordset("Legal Masterlist")

End Sub

*******************
Run-time error '3024'
Cannot find file 'LegalDB.mdb'
*******************
Above is the error that I encountered.

For the "onClick" event procedure at the save button, below is the code:

Private Sub cmdSaveEmployee_Click()
With rstLegalMasterlist
![EmployeeID] = Me.EmployeeID
.Update
.Close
End With

dbsLegalDB.Close

End Sub

****************
Run-time error '424'
Object required
****************
Is there anything wrong with the code ?

Regards

 

[Marshall Barton]

I have two Forms: Legal Masterlist and Employee.

The recordsource for each form is "Legal Masterlist" Table and "Employee"
Table.

When I clicked a button in the "Legal Masterlist" form, it'll open up
another form called "Employee". After filling up the latter, a save button is
clicked to save the EmployeeID into the "Legal Masterlist" Table.

I have an Event Procedure for "onLoad" in the "Employee" form that declares
the database and recordset. There is also an "onClick" event procedure for
the save button in this form. Below is the code:

Private Sub Form_Load()
Dim dbsLegalDB As Database
Dim rstLegalMasterlist As Recordset

Set dbsLegalDB = OpenDatabase("LegalDB.mdb")
Set rstLegalMasterlist = dbsLegalDB.OpenRecordset("Legal Masterlist")

End Sub

*******************
Run-time error '3024'
Cannot find file 'LegalDB.mdb'
*******************
Above is the error that I encountered.

For the "onClick" event procedure at the save button, below is the code:

Private Sub cmdSaveEmployee_Click()
With rstLegalMasterlist
![EmployeeID] = Me.EmployeeID
.Update
.Close
End With

dbsLegalDB.Close

End Sub

****************
Run-time error '424'
Object required
****************
Is there anything wrong with the code ?

The first error is probably because you are not specifying
the complete path to the mdb file.

The second error is because the database and recordset
variables are local to the Load event procedure.

I don not understand why you would want to go through all
this. Why not just bind the employee form to it's table?

 

[lhtan]

After changing from "Private" to "Public", the error message still appears
when cllcking the "save" button.

****************
Run-time error '424'
Object required.
****************

Purpose: To save the EmployeeID into the "Legal Masterlist" Table, otherwise
information about which employee handles a record would be lost. The
"Employee" Form recordsource is the Employee Table. It's just for user to
retrieve an Employee record without having to key in the same info
repeatedly. So with the "EmployeeID" that appears on the form, I want to save
it into the "Legal Masterlist" Table.

Would you think there's a better way to achieve it or my exisitng code can
be improved ?

Regards

I have two Forms: Legal Masterlist and Employee.

The recordsource for each form is "Legal Masterlist" Table and "Employee"
Table.

When I clicked a button in the "Legal Masterlist" form, it'll open up
another form called "Employee". After filling up the latter, a save button is
clicked to save the EmployeeID into the "Legal Masterlist" Table.

I have an Event Procedure for "onLoad" in the "Employee" form that declares
the database and recordset. There is also an "onClick" event procedure for
the save button in this form. Below is the code:

Private Sub Form_Load()
Dim dbsLegalDB As Database
Dim rstLegalMasterlist As Recordset

Set dbsLegalDB = OpenDatabase("LegalDB.mdb")
Set rstLegalMasterlist = dbsLegalDB.OpenRecordset("Legal Masterlist")

End Sub

*******************
Run-time error '3024'
Cannot find file 'LegalDB.mdb'
*******************
Above is the error that I encountered.

For the "onClick" event procedure at the save button, below is the code:

Private Sub cmdSaveEmployee_Click()
With rstLegalMasterlist
![EmployeeID] = Me.EmployeeID
.Update
.Close
End With

dbsLegalDB.Close

End Sub

****************
Run-time error '424'
Object required
****************
Is there anything wrong with the code ?

The first error is probably because you are not specifying
the complete path to the mdb file.

The second error is because the database and recordset
variables are local to the Load event procedure.

I don not understand why you would want to go through all
this. Why not just bind the employee form to it's table?

 

[Marshall Barton]

After changing from "Private" to "Public", the error message still appears
when cllcking the "save" button.

****************
Run-time error '424'
Object required.
****************

Purpose: To save the EmployeeID into the "Legal Masterlist" Table, otherwise
information about which employee handles a record would be lost. The
"Employee" Form recordsource is the Employee Table. It's just for user to
retrieve an Employee record without having to key in the same info
repeatedly. So with the "EmployeeID" that appears on the form, I want to save
it into the "Legal Masterlist" Table.

Would you think there's a better way to achieve it or my exisitng code can
be improved ?

You should have gotten a compile error because you can not
have Public local variables. That's kind of an oxymoron.

The least you can do is move the
Dim rstLegalMasterlist As Recordset
line up above the first Sub/Function in the module. This
will make it a module level instead of local to a procedure.

(Note: your line dbsLegalDB.Close would be illegal if Access
wasn't dmart enough to just ignore it. If you didn't open
something, then don't try to close it. Setting it to
Nothing would be more appropriate if it were in the same
procedure that set the database variable.)

I still think you should scrap all this glop and just bind
the form to the Legal Masterlist table. The record will be
saved automatically when the form is closed.

 

[lhtan]

I too wish that it could be simplified but this would not provide
user-friendliness to the person doing data-entry.

Regards

 

[Marshall Barton]

If you'll explain what "user-friendliness" capability you
are trying to provide, there may be a simpler way to get
there.

Did you get your code to work?