Help With VBA - DAO Recordsets

[mzanzilla]

I just got introduced to Access VBA and recordsets, and I have been trying my hands on a few codes. Using recordsets, I am able to navigate through records, search, etc. The issue now is this:

I have three tables - tblEmployees, tblDepartments, tblPayrate

A primary key is used to link tblEmployees with tblDepartents and tblPayrate

I have an unbound form that uses recordsets to navigate and manipulate data in tblEmployees.

I want to display data from the related tables (create a subform) and update all tables with information the user enters (using recordsets). However, I am not certain how to proceed with this. I'm new to this and any help will be greatly appreciated.

 

[GinaWhipp]

Well, if you are adding records then look at an APPEND query. If you are updating records then look at an UPDATE query. You can DoCmd.RunSQL and use the VB Editor or you could just right the query and use DoCmd.OpenQuery "YourQueryGoesHere"

 

[mzanzilla]

Thanks, Gina

I'm currently using the recordset's AddNew method to add records to the main table. I have also used a SQL statement to add records. However, the problem arises when tyring to make it so that the main form displays its related records in the subform. I guess what I'm trying to do is to link the unbound subform to the unbound main form.

 

[GinaWhipp]

That won't happen with a RecordSource and fields to link on. Why are you using unbound forms?

 

[ctags009]

have you tried using a list with a rowsource of the data you want to display? then, perhaps use me.LISTNAME.refresh after your .addnew/.update code

 

[mzanzilla]

I'm exploring that option - use the recordset as the rowsource on the subform. I'm new to this and trying to figure out how to implement that. I'm picking bits and pieces here and there. Thanks.

 

[mzanzilla]

Reason for me wanting to use unbound forms is that the database sits on a shared network drive. Currently the forms are bound and I'm trying to steer away from that because users start creating a record and don't complete the task. Because of Access' auto save feature, blank records are created. What I have done with the unbound forms is create a save button that requires the user to save the record before moving on. To answer the question why I want to use unbound forms: for flexibility.

 

[GinaWhipp]

Okay, well let us know how the Refesh works... If it doesn't work we're going to need to see the code.

 

[ctags009]

In response to:

Reason for me wanting to use unbound forms is that the database sits on a shared network drive. Currently the forms are bound and I'm trying to steer away from that because users start creating a record and don't complete the task. Because of Access' auto save feature, blank records are created. What I have done with the unbound forms is create a save button that requires the user to save the record before moving on. To answer the question why I want to use unbound forms: for flexibility.

Setting the "required" property on the table itself should prevent users from partially entering records. You can also be very specific with what you allow users to enter by either using the data validation on the tables themselves OR what I tend to do is on the "SUBMIT" buttons onclick event I'll use IF statements to make sure the data is complete and valid (such as an end date being later than the start date) and if not, have a message box pop up and let the user know that the data is invalid or incomplete.

 

[smig]

rs.AddNew method is simply to add new record to the recordset.

This is not what you want to do here. You want to UPDATE the record. for this you should use the rs.Edit method (With rs.Update at the end)

But as I understand it you want to update a set of records, Not a single one. For this it will be much easier to use an Update query.
You can create the query in the query builder and run it from code:
CurrentDB.Execute QueryName, dbFailOnError

 

[mzanzilla]

After hours getting lost in the Access world, I finally figured it out that an append query works just fine. I add the record, then store the primary key generated into a variable. I then use an append query to append the records in the related table using the primary key that was stored in memory.