Problem with variable values (1 Viewer)

[b0gdan]

Hi i have a problem with a variable value...I have 2 variables and i need them to take values from tables.

Dim dosarid As Integer
Dim cnp As String
………………………………………………………………………………………………
DoCmd.RunSQL "INSERT INTO tblDosare (DenumireDosar,CodDosar,DataDosar,Instanta) VALUES ('" & Me.txtDenumireDosar & "','" & Me.txtNrDosar & "','" & Me.txtDataDosar & "','" & Me.cmbInstanta & "')"
dosarid = DLookup("[DosarID]", "tblDosare", "[CodDosar]=" & Me.txtNrDosar & "" & " AND" & "[DataDosar]= #" & Month(Me.txtDataDosar) & "/" & Day(Me.txtDataDosar) & "/" & Year(Me.txtDataDosar) & "#")
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 & "')"
cnp = DLookup("[CNP]", "[tblPersoaneFizice]", "[CNP]='" & Forms!frmPrincipal!subfrmTipVersus!txtCNP & "'")DoCmd.RunSQL "INSERT INTO RDPF (DosarID,CNP) VALUES (dosarid,cnp)"


where DosarID is autonumber and CNP is text.
The problem is that in execution the varibles do not take those values from the table, instead a message appear "insert parameter value" and i have to give values for those variables, but i want those variables to take values from those 2 tables: tblDosare, tblPersoaneFizice.

If anyone can have a solution....
Thanks

 

[gemma-the-husky]

i think in dosarid = dlookup( ... etc, you need a space after the first AND, but it looks like you have syntax error in there because you have an & inbetween quotes I think. try setting that whole condition into a string, and then displaying it with a msgbox to make sure it looks right, before you use it.

put a break point before the dlookup and then step through code line by line, so you can see how your variables change

 

[The_Doc_Man]

Invariably when working with variables in dynamic SQL, you must remember two basic rules.

1. You have to concatenate the data you want in a text format because that's the way SQL wants it.

2. When you concatenate it, you must also consider the format in which it will be used BY the SQL processor as well as the way SQL itself wants to see it.

So to concatenate a text value that will be part of a WHERE a = "text" clause (in other words, A is a text field), you must assure that "text" appears in quotes. This usually requires doubled quotes inside the quoting string because of the way the string processor eliminates quotes.

However, if you use WHERE b = some-number (b is a numeric field), that some-number must NOT be in quotes by the time SQL "sees" it. BUT it still must be presented in text format because SQL wants to see it as part of the (text) SQL query string.

Confusing? You betcha. But that is the nature of the beast when you are doing dynamic SQL strings.