Linked Form/Table

[Vonbare]

Hi

I am creating a database but I am having a problem with one little bit (so far!!)

I have form (and table) 1 and form/table 2. When I am in form1 (which is company details) I want to be able to click a button which will open form 2 and allow me to enter a new record of incoming mail details regarding the Co. selected in form 1.

I have made a relationship between the Company ID on both tables.

I cannot seem to work out how to make Access take the Co ID no from form/table 1 and stick it into form/table 2 when I push the button. It is probably very simple, but I can't work it out.

I have tried an append query, but this tries to update all records in table 2 with the new Co ID info in table 1.

Can anyone offer any advice??

thanks heaps
Siobhan.

 

[quest4]

Is this a form/subform or two separate forms? If it is the form/subform, I would check the parent/child relationship in the form and subform properties. hth.

 

[Vonbare]

no, it's two separate forms.

I don't know how clear my question is..... when I click on the button on the first form, I need to send the Co ID to the 2nd form/table and open a new record.

thanks heaps.

 

[quest4]

I would use something like this to open the second form:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "YourFormName"
stLinkCriteria = "[CoID]=" & Me![CoID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
This is in the OnClick event of the cmdButton. I have assumed, which is always dangerous, the CoID is the field you are linking, so the just add the second forms name and away you go. You might have to add:
DoCmd.GoToRecord , , acNewRec
This should get you in the ball park. hth.

 

[Vonbare]

hmmm....you are right - I am using the CoID to link the two.

I had a look and that code was already in there. But now something even stranger is happening and when I insert info into form 2, it is not adding any records in table 2.

 

[quest4]

If you decided to use the newrec then that is exact you you are getting, the second form opening to the CoID and ready to add a new record.

 

[Mile-O]

What tables do you currently have? Linking both tables by CompanyID, at least to me, sounds suspicious.

Also, never bind a table to a form. Use queries for that.

 

[quest4]

Interesting, I rarely use queries, but then I have a lot of code in the forms. I use recordsets to do the things I want to do. But it is always good to get other points of view.

 

[Vonbare]

the tables that I'm using in this are: Company Details (tbl1) and RFM (tbl2). And I am using the CoID as the primary and foreign key in the respective tables.

when I open fm1 and choose the relevant company, I then click to open fm2 and am trying to enter details into tbl2.

What has been happening is that, I will open fm2 and will see the correct Co details in the form header. I then will enter a new record in the detail which should update tbl2. This was working except for whatever reason (probably because I haven't set it up right!!) the company details is not being transfered from tbl1 to tbl2.

Now nothing is being put into tbl2.

 

[Vonbare]

I have now gone and bound all my forms to queries instead of directly to the tbls now. This hasn't changed any of the above....