Using INSERT INTO and trying to pick up an ID number from a from 3

[Powerhouse123]

Hi,

I have a table which acts as a history for a child's Assessment form (done through subforms). When a new assessment is created for a child I want the history table to be populated with a couple of default records.

I've managed to work out how to use the INSERT INTO function and get the required default (prepopulated) values to appear in the history table BUT... because the history is linked by an assessment form ID in its table they are not linked and don't appear on the form...because they are not linked.

How can I pick up the newly created assessment form ID so that I can place that at the same time into my INSERT INTO statement to the history table when adding the two default records so that the assessment history default records are linked to the assesssment thus showing on the form?

Here is my INSERT INTO code which works fine but I need to enhance it to include the newly created assessment form ID number:

What I've tried to do is use the assessment ID txtbox on the form to pick up the ID number and place it into the assessment history table but this doesn't work.

DoCmd.RunSQL "INSERT INTO tblAssessmentHistory ([HistoryDescriptionID],[AssessmentID]) VALUES ('1','Forms!frmChildAssessmentDetails!frmAssessment_subform!AssessmentID');"

Any help offered would be greatly appreciated.

Apologies if this is the wrong forum.

Thanks in advance!

 

[PHV]

DoCmd.RunSQL "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1',Forms!frmChildAssessmentDetails!frmAssessment_subform.Form!AssessmentID)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Powerhouse123]

Hi PHV,

Thanks for the code tweaks..

Tried it but now it comes up with a query box asking for a value to be inserted for that Assessment ID field.

I entered the one I wanted but it still didn't work...

I don't think this is a massive problem, it's just working out what code structure it will accept, I think the basis is already there...

Thanks

 

[Powerhouse123]

Hi All,

I've tried the following code to take a different route but still no joy...am I doing this right? It appears to be having a type mismatch when appending to the new table and not working so it's not picking up the value more just the name of the field...which then causes the type mismatch...

Dim AssessmentValue As Variant
AssessmentValue = "Forms!frmChildAssessmentDetails!frmAssessment_subform!txtAssessmentID"


DoCmd.RunSQL "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1','AssessmentValue')"

If not I revert back to the code below from PHV which gives me a query box to complete rather than just taking the assessment ID number and appending it automatically?

DoCmd.RunSQL "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1',Forms!frmChildAssessmentDetails!frmAssessment_subform.Form!AssessmentID)"

Please help!

Many thanks as always, it's got to be something simple, it works fine without picking the up assessment ID but I need that in order to make the link between the assessment and the history!

Cheers

 

[PHV]

What about this ?
AssessmentValue = Forms!frmChildAssessmentDetails!frmAssessment_subform.Form!txtAssessmentID
DoCmd.RunSQL "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1'," & AssessmentValue & ")"


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Powerhouse123]

Thanks so much for help BUT...

It didn't work, just did the same as before...pops up with a query box which if I insert the assessment ID number works but I want this to be seamless without my intervention!

Sorry!

It's so annoying!

 

[PHV]

Use the expression builder (loaded forms ...) to discover the proper syntax for retieving the AssessmentID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Powerhouse123]

This is going to sound very school boy like and ignorant but having got up the expression builder and drilled down to the assessment ID field through various ways (including the loaded forms) all I get is [txtAssessmentID] but that doesn't give me the full syntax path I would have thought you were talking about...

Could you explain how I can get the full path please?

Very sorry to be a pain...

 

[PHV]

What about this ?
AssessmentValue = Me!txtAssessmentID
DoCmd.RunSQL "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1'," & AssessmentValue & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Powerhouse123]

Thanks BUT...

Same as before, I get the query pop up box again!

Sorry!

 

[Andrzejek]

What about this ?

Dim AssessmentValue As Integer
Dim strSQL as String

AssessmentValue = Me!txtAssessmentID

strSQL = "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1'," & AssessmentValue & ")" 

Debug.Print strSQL[green]
''' What do you get in Immediate Window ???[/green]

DoCmd.RunSQL strSQL

Have fun.

---- Andy

 

[Powerhouse123]

Thanks Andy...

I got the following message in the Immediate window...

INSERT INTO tblCAFHistory (HistoryDescriptionID,CAFID) VALUES ('1',)

Does this help?!

Cheers

 

[Powerhouse123]

Apologies,

That should read...

INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1',)

Thanks

 

[Andrzejek]

Now we know you don't have anything in [tt]Me!txtAssessmentID[/tt]

How do you get the value into [tt]Me!txtAssessmentID[/tt] field???

Have fun.

---- Andy

 

[jsteph]

Powerhouse,
Is AssesmentID an integer, Long integer, string, etc? I ask becuase the code above
AssessmentValue = Me!txtAssessmentID
has AssessmentValue as Integer, and no error checking, so if the value is null or non-integer, this would result in the blank you see in the sql.

Also, for the prior code...
Access Subforms have *two* properties that often get confused: "Name" and "Source Object". The Source Object is that actual form name of the subform. However, when referring to it in code you must use the Name property, which is not necessarily the same.
--Jim

 

[Powerhouse123]

Hi All,

Thanks so much for your efforts. Between everyone's suggestions I've managed to get the functionality working as I wanted. In terms of the neatest way of doing it, I can't be sure but for now, it works so here is what my code looks like for anyone else who has a similar issue!

Forms!frmChildAssessmentDetails.Form.Requery
DoCmd.SetWarnings False

Dim AssessmentValue As Variant
AssessmentValue = Forms!frmChildAssessmentDetails!frmAssessmentsubform.Form!AssessmentID

DoCmd.RunSQL "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID, PractitionerID) VALUES ('2'," & AssessmentValue & ", '573')"
DoCmd.RunSQL "INSERT INTO tblAssessmentHistory (HistoryDescriptionID,AssessmentID) VALUES ('1'," & AssessmentValue & ")"

Forms!frmChildAssessmentDetails.Form.Requery
DoCmd.SetWarnings True

Many, many thanks as always!

Powerhouse