"insert Parameter Value" (1 Viewer)

[b0gdan]

Hi,
I have a problem with a variable value. I have 2 tables calles FIles and Persons and i want to create a relationship between them one-to-many (More files for the same person) because a file can have the same number of file but not the same date. So for this relationship i have created a third table having as rows the primary key of the Files table and Social security number from the Persons (because in the persons table the social security number is unique).
For this i had created 2 variables and gave them as primary value the value zero.
After i insert in the tables with DoCmd.RunSql data about files and persons i assign to that 2 variables the values of the primary key and social security number:

Dim dosarid As Integer
Dim pfid As Integer
dosarid = 0
pfid = 0
DoCmd.RunSQL "INSERT INTO tblDosare (DenumireDosar,CodDosar,DataDosar,Instanta) VALUES ('" & Me.txtDenumireDosar & "','" & Me.txtNrDosar & "','" & Me.txtDataDosar & "','" & Me.cmbInstanta & "')"
DoCmd.RunSQL "INSERT INTO tblActIdentitate (Tip,Serie,Numar,CNP,DataEmitere,Emitent) VALUES ('" & Forms!frmPrincipal!subfrmTipVersus!cmbTipActId & "','" & Forms!frmPrincipal!subfrmTipVersus!txtSerie & "','" & Forms!frmPrincipal!subfrmTipVersus!txtNumarAct & "','" & Forms!frmPrincipal!subfrmTipVersus!txtCNP & "','" & Forms!frmPrincipal!subfrmTipVersus!txtDataEmitere & "','" & Forms!frmPrincipal!subfrmTipVersus!txtEmitent & "')"
dosarid = DLookup("[DosarID]", "[tblDosare]", "[DenumireDosar]='" & Me.txtDenumireDosar & "'")
Me.txtDenumireDosar = ""
Me.txtNrDosar = ""
Me.txtDataDosar = ""
Me.cmbInstanta = ""
Me.cmbLocalitate = ""
Me.cmbTipVersusDosar = ""

If Me.subfrmTipVersus.SourceObject = "frmPersoanaFizica" Then
DoCmd.RunSQL "INSERT INTO tblPersoaneFizice (Nume,Prenume,CNP,Localitate) VALUES('" & Me!subfrmTipVersus!txtNume & "','" & Me!subfrmTipVersus!txtPrenume & "','" & Me!subfrmTipVersus!txtCNP & "','" & Me!subfrmTipVersus!txtLocalitate & "')"
pfid = DLookup("[PersoanaFizicaID]", "[tblPersoaneFizice]", "[CNP]='" & Me!subfrmTipVersus!txtCNP & "'")
DoCmd.RunSQL "INSERT INTO RDPF (DosarId,PersoanaFizicaID) VALUES (dosarid,pfid)"

This is not all the code but the problem is: The code works fine but when it comes to the variables a message appear "insert parameter value" and the variables does not take the values from the table with dlookup.

If anyone can suggest a solution i will appreciate it.
Thanks!!

 

[MStef]

Hello Bogdan!

In DLookup try this:

...............& Me!MainFormName!SubformName!Fieldname & "'")

 

[b0gdan]

Thanks but...

This will not work because Me stands for Forms!MainFormName.
I am more interested in how can i take those values from table with dlookup instead of typing them when "insert parameter value" occurs.

 

[MStef]

Then try:
..........& Forms!MainFormName!SubFormName!FieldName & "'")

 

[b0gdan]

Same problem

I had tried. The program works the same but the same problem occurs: the variables do not take the values from the table with dlookup. I don`t know what to do because errors do not occur...
Thanks

 

[MStef]

Is the field CNP numeric or text.

 

[b0gdan]

Cnp..

CNP is text i corrected and declare it string and not gave initial value zero but still don`t work

 

[jkl0]

Not sure if this will work but try.

..........& Forms!MainFormName!SubFormName!Form!FieldName & "'")