Form 1 answers fill in Form 2's Table

[NickWren]

I don't know the easiest way of doing this, but I have tried a couple of different ways of trying to write the data.

Right now I have 4 textboxes that call upon an answer in form 1
Controlsource:

=[Forms]![PolandNETSQForm]![Wren ID]
=[Forms]![PolandNETSQForm]![Sample Name]
=[Forms]![PolandNETSQForm]![Test Type]
=[Forms]![PolandNETSQForm]![Facility]

This pulls the info from PolandNETSQForm (Form 1) and brings it to my second form.
Next using VBA scripts I write these answers to form 2

Private Sub Form_Load()
Facility = Me.FormFacility
Wren_ID = Me.FormWrenID
Sample_Name = Me.FormSampleName
Test_Type = Me.FormTestType
End Sub

Then I use another VBA script that is based off a button I made called Command708
This code however does not take the information from the table and bring it to the next table or even the form doesnt bring it over.

Private Sub Command708_Click()
DoCmd.RunSQL "INSERT INTO PolandNETResults (Wren_ID, Test_Type, Facility, Sample_Name) VALUES (" & Wren_ID & ", " & Test_Type & ", " & Facility & ", " & Sample_Name & ");"
CurrentDb.Execute "INSERT INTO PolandNETResults (Wren_ID, Test_Type, Facility, Sample_Name) VALUES (" & Wren_ID & ", " & Test_Type & ", " & Facility & ", " & Sample_Name & ");"
DoCmd.Save([acForm],[PolandNETResults Query])
Me.Requery
End Sub

So is there a better way of entering those 4 fields so they fill out both forms (they need to be connected anyways, one is a status page the other is a results page for a sample)
Or is there a way that I can get one form to either write in new data if it doesnt exist and if it does exist just open up the existing data.

[June7]

Why are you duplicating data between tables? Should probably only save primary key as foreign key into second table.

Why are you using code to write data? Are you not using bound forms? Should you be using a form/subform arrangement for data entry/edit?

If you want to provide db for analysis, follow instructions at bottom of my post.

[NickWren]

I want to actually have the form submit the data twice if possible, one to the table called "PolandNETS" and to a table called "PolandNETResults"
where "Wren ID", "Sample Name", "Facility", and "Test Type" are the same for both tables (maybe the best thing would be just adding on to that table so that the results will be at the end giving you a matlab, progressive, and netscore already associated with all those fields)

DatabaseOneFacilityAccessForum.zip

Also if you know how to do a search query with an input mask as: L0-/00 (ex. B1-01) also it will need a if left blank bring up "*" (All including nulls)

A better search form would also help out, I want one where I can type in either the Wren ID or Sample Name (these are unique identifiers Wren ID will never be repeated however Sample Name might but we might want multiple results for that one sample name)
but I want the search for to auto populate the rest of the fields as that one field is being entered.


I found something called RecordSet I dont know if this will help me get the previous forms records and put them to another table

'1)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT TheseFields FROM ThisTable")
With rs
  .Add
    .Fields("ThisField") = ThisValue
    .Fields("ThatField") = ThatValue
  .Update
End With
rs.Close
Set rs = Nothing
'2)
CurrentDb.Execute  "INSERT INTO ThisTable (ThisField, ThatField) VALUES (""" &  ThisValue & """, """ & ThatValue & """)"

[June7]

Filter parameters can use wildcards. Cannot use input mask.

Review http://www.allenbrowne.com/ser-62.html

So you want to use compound primary/foreign key - I avoid this as much as possible.

Since you have WrenID in PolandNETS why do you need the 3 other fields also in PolandNETS? WrenID is not unique in PolandNETResults?

I don't really understand this relationship. Shouldn't Results be the dependent table?

What is CalcID for?

Your forms are all bound. Why are you using code to save data? Why don't you use form/subform arrangement?


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.