Subform populating with combo selection

[parkerkay]

OK.

I had to build a database before that did something similar to what I'm trying to accomplish. I am trying to get a subform to populate based on a combo box selection...thing is, it needs to append records to a table to work. Here is the link to my db:

http://www.4shared.com/file/57294903/85df8c87/zipstuff.html

Contaminants and Analytes need to be associated. The both have list forms to manage data. Then frmConAna is where the association is made.

Now, on my data entry form, frmSample, I want to select a contaminate from the combo box, and then have my subform populate with as many 'blank' records as there are analytes...these records should contain the name of the analyte. Then the user should be able to fill in the rest of the fields (some of the fields are calculated). QrySampleDetails brings all the fields together for the subform, and performs the calculations.

I wasn't sure how to use Access to link the forms properly. Someone helped me on another database to link a subform to main form through code (not really link, but act like it's linked, and write code to make up for the missing linkage). Don't get me wrong...I DID try to link with Master/Child fields...however, it didn't work. I tried with the ContaminantID and SampleID. I took the code from that other db, tried to trace it and apply to this database.

I put all the code in the AfterUpdate event for the Contaminant combo box. I am getting an error that says "number of query values and destination fields are not the same"

I had to change the code a little bit to get that error message, I kept getting absolutely ridiculous error messages until I got the code looking like it is now.

Could someone take a look and possibly tell me what I've done wrong?

Thanks!!!

 

[TheAceMan1]

How are ya parkerkay . . .

I had a look at the Db. The 1st place I go to get an Idea of whats going on is the [blue]Relationships Window.[/blue] This tells just about all!

All I can say is [blue]Wow![/blue] . . . I see circular referencing, indeterminants and a [blue]many to many[/blue] thats not quite making sence!

I strongly suggest you hop on over to forum700, explain your table structure there and get your tables/relationships right 1st. Then come back here if the form problem still exists.

I reall do wish I had better news. You can make the changes now or later . . . the choice is yours!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

the error message makes sense:

""INSERT INTO tblSampleDetails (SampleID, AnalyteID) SELECT " & Me.ContaminantID & ", AnalyteID, SampleID FROM tblConAna WHERE ContaminantID = " & Me.ContaminantID & " ORDER BY ConAnaID""

INSERT INTO tblSampleDetails (SampleID, AnalyteID)
2 Fields
& Me.ContaminantID & ", AnalyteID, SampleID
3 Fields

I think you mean this
DoCmd.RunCommand acCmdSaveRecord
sql = "INSERT INTO tblSampleDetails (SampleID, AnalyteID) SELECT " & Me.SampleID & ", AnalyteID FROM tblConAna WHERE ContaminantID = " & Me.ContaminantID & " ORDER BY ConAnaID"

Get rid of those bogus tables and relations for this to work.

 

[MajP]

Also I think you want the subform linked to the main form by
Child SampleID
Parent SampleID