Input data into 2 tables from the same form

[kgerlitz]

I have a form that puts information into multiple fields of the "databasehistory" table. Within this same form I would like to have the ability to write information to the "apstatus" field of the "databaseparents" table. I would like to do this without a subform as this form needs to be in continous mode. I tried to add the "databaseparents" table to the record source of the form but ended up with a bunch of '#name' results in all my fields. I don't know if I need to set up a relationship (this concept I have never grasped thus have never done it).

Is what I want to do possible?? I know enough to be very dangerous but not nearly enough to solve the simpliest of issues. Thank you in advance for your help.

 

[Remou]

Hi
How about using a recordset?
[tt]Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset _
("Select apstatus From databaseparents Where ID=" & Me.ID)
rs.Edit
rs!apstatus=Me.apstatus
rs.Update[/tt]
Or there abouts.

 

[kgerlitz]

I was afraid someone was going to suggest a recordset. I have zero experience on that as well. If you could point me in the right direction I'm pretty good at figuring things out.

Where do I put the code you wrote and how do I access it? Thanks for your help

 

[Remou]

That depends on how you see your database working. Let's say that you want to write to the table when an unbound textbox called txtApStatus is updated. You would put the code in the After Update event of txtApStatus. You also need a way of saying which record is to be updated; the snippet above uses an ID field [tt]Where ID=" & Me.ID[/tt].

I would find it easiest to do the whole thing in a message box first, so I could see what was going on.

Private Sub txtApStatus_AfterUpdate()
'A reference to the Microsoft DAO 3.x Object Library is needed
Dim rs As DAO.Recordset

'Open the recordset, using ID on the form to match ID in the table
Set rs = CurrentDb.OpenRecordset _
   ("Select ID, apstatus From databaseparents Where ID=" & Me.txtID)
'Look at what's in the table and what is on the form
MsgBox rs!ID & " " & rs!apstatus & " " & Me.txtID & " " & Me.txtApStatus
'Uncomment the three lines below when it looks ok.
'rs.Edit
'rs!apstatus = Me.txtApStatus
'rs.Update
End Sub

So I would try the above and then if it looked ok, I would comment out the message box and put the rs stuff back in.

If you want to say a little more about how you see your form working, I might be able to say more exactly.

 

[MajP]

Here is an easy way. It sounds like you have information from 2 unrelated tables that you want to work on from the same form. You want the information to appear as two continous looking tabular forms. Build two tabular forms "subFrmDataBaseHistory" and buld "subFrmDataBaseParents". Build a third form "frmDBHistoryAndParents". This is an unbound form. Put 2 sub forms on "frmDBHistoryAndParents" one for each sub form. Now you have 2 unrelated tabular forms on one form and you can work on both.
This will do what you say, but the fact that you want to do this hints of some poor database design. Seriously, do yourself a favor and read about relational database design and normalization, there are some good primer links from this site. Using a database and not understanding relationships and normalization is about as good as knowing how to fly a plane except for the take off and land part.

Good luck

 

[kgerlitz]

Remou, thanks for your help. I am working on your code and using it as a platform to educate myself on recordsets.

MajP, thanks for your input as well. Your idea was something I considered. You nailed it on the head in that I know enough to be dangerous but not enough to be effective. I built this thing on the fly and did not intend it to get as big as it is. At this point, I agree that I need to read more before I go much further.

Thanks again to both

 

[Remou]

Recordsets are great, once you get the hang of them.

I should have mentioned that if you have an unbound text box on a continuous form, it will show the same thing for all records, so if you are using an unbound textbox, it may be best to put it in the form header or footer.

The links below are gathered from posts in these fora. The first two are the same, much recommended, article in two different formats.
Relational links:

http://www.alphabetcitydataworks.com/fundamentals.htm

http://r937.com/relational.html

283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database