Aarrgghhh!

[Vonbare]

Please can anybody help me!!!!

I have two tables. One with Company details. The other to log all incoming mail we have received in our office. I was thinking that as opposed to typing in the company each time, it would be better to have the tables linked.

What I want is to be able to choose a company on form 1, click on a button and be able to add a new record to table 2, form 2.

At the moment I am trying to do this with linked forms, but it's really not working. For some reason they are not linking. I think i have the relationships right (Company ID to Date Rec'd - one to many) but no matter what I do it is not linking them, or even sometimes (whilst I have been playing) not even putting any data into table 2.

Can anyone suggest why this would be happening, or perhaps if there's an easier way of doing it.......

I don't know visual basic or sql really at all, so will be doing it without!!

thanks in advance
VB

 

[ScottGem]

Please can anybody help me!!!!

I have two tables. One with Company details. The other to log all incoming mail this company has sent.

I want to be able to choose a company, click on a button and be able to add a new record to table 2.

At the moment I am trying to do this with linked forms, but it's really not working. For some reason they are not linking. I think i have the relationships right (Company ID to Date Rec'd - one to many) but no matter what I do it is not linking them, or even sometimes (whilst I have been playing) even putting any data into table 2.

Can anyone suggest why this would be happening, or perhaps if there's an easier way of doing it.......

I don't know visual basic or sql really at all, so will be doing it without!!

thanks in advance
VB

CompanyID to Date Rec'd does not sound like the correct linkage. Your Mail log table should have CompanyID as a foreign key. You should use a main form bound to the Company table and a subform bound to the Mail log linked on CompanyID. When you add a record int he subform, the CompanyID will automatically be filled in. As you change records onthe main form, the subform will only display log records for the current selected company.

 

[ColinEssex]

I think what you're describing is a classic one-to-many relationship. You don't link Forms, you link tables.

In your company table you should have a companyID (an AutoNumber) this should be linked to the CompanyID in Table2 (not an AutoNumber)

Link via the Relationships window. Then when you create the Company form you can have a SubForm for the Table2 data to be input. Create the SubForm via the wizard and it'll take you through the appropriate steps to link correctly

Col

 

[Vonbare]

ok

Thanks for that!

ColinEssex:
I'm trying this at the mo. Do I want to force referential integrity between the tables? (one to many).

Should I use queries for the forms? Some people recommend against linking a form straight to the table.

VB

 

[Vonbare]

no luck

I've tried this (both with and without referential integrity) and it's not working. It's not filling in the company details (or linking it!)

just had a thought - would this be because I am running my forms off queries....but they are seperate queries....ie: one for company and one for incomign mail....?

 

[ScottGem]

I've tried this (both with and without referential integrity) and it's not working. It's not filling in the company details (or linking it!)

just had a thought - would this be because I am running my forms off queries....but they are seperate queries....ie: one for company and one for incomign mail....?

Are you using a main form/subform? You can use queries for the recordsources as long as there are no joins. The ONLY detail you want in the maillog is the CompanyID. You can get the rest (for reporting purposes) by joing the 2 tables. If the subform is linked on CompanyID the CompanyID will be automatically filled in.

 

[Vonbare]

sniff

I have just tried that....it's not working....it's really not....I don't get it! I wish I could put the test database on here so anyone could look at it!!!


If it helps at all this is what the code is on the button access automatically creates....
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![please1].DataEntry = True
Else
Forms![please1].Filter = "[Company ID] = " & Me![Company ID]
Forms![please1].FilterOn = True
End If

End Sub
Private Sub OpenChildForm()

DoCmd.OpenForm "please1"
If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub
Private Sub CloseChildForm()

DoCmd.Close acForm, "please1"
If Me![ToggleLink] Then Me![ToggleLink] = False

End Sub

 

[ScottGem]

You are obviously NOT using a subform, but instead trying to use a synchronized form. I strongly suggest using a mainform/subform.

You CAN post your test database here. ZIPO it up and use the Attach files option.

 

[Vonbare]

I will try that tomorrow - I was using a linked form - because i wanted it to open on a brand new form to input the incoming mail data.

thanks for your help.

 

[ScottGem]

I took a look at your file. The form tblCompanyDetails seems to work exactly as I suggested. It makes more sense to use a mainform/subform here since you can then see all the log items for a company.

The reason why the synchronized forms don't work is you are doing nothing to populate the data. Synchronized forms work best when viewing or editing existing records. But you are trying to add records.

If you really want to do this, You need to pass the CompanyID value to second form (I would do this using the OpenArgs argument). Then test to see if you have a new record. If you do, set the CompanyID control to Me.OpenArgs

 

[Vonbare]

thanks

Thanks Scott - I will try that today and see how I get on!!

cheers!

 

[Vonbare]

bugger!

ok - so apparently that only works for 2000 upwards....I'm working (due to my sh1tty workplace!!) in Access 97.

With regard to using a subform - I don't want to be able to see more than one (the new) record at a time on the incoming mail form.

Can anyone help with this then?

 

[ScottGem]

ok - so apparently that only works for 2000 upwards....I'm working (due to my sh1tty workplace!!) in Access 97.

With regard to using a subform - I don't want to be able to see more than one (the new) record at a time on the incoming mail form.

Can anyone help with this then?

What doesn't work? OpenArgs works in A97/

Why would you not want to see all the log entries? But if you don't use Form view instead of continuous form or datasheet view. If you want to restrict it to just the most recent, use a Query that returns the TOP 1 record.