Updating Zero Rows

[reubenpatterson]

Hi, I'm really new to this, so here it goes...when I run this it runs without errors, but says "Updating 0 rows" and nothing is written to the table. What am I missing here?

Dim AcctSysprin As String
AcctSysprin = (Forms![FrmEsc4Disp]![AcctNumb])
AcctSysprin = Left$(AcctSysprin, 8)

Dim FormSubmit2 As String
FormSubmit2 = "INSERT INTO TblDateContact"
FormSubmit2 = FormSubmit2 + " (FldCity)"
FormSubmit2 = FormSubmit2 + " SELECT FldMarket"
FormSubmit2 = FormSubmit2 + " FROM TblMarket"
FormSubmit2 = FormSubmit2 + " WHERE FldSysprin = '& AcctSysprin &' "

DoCmd.SetWarnings True
DoCmd.RunSQL FormSubmit2
DoCmd.Save

 

[PHV]

You may try this instead:

FormSubmit2 = "INSERT INTO TblDateContact (FldCity)" _
 & " SELECT FldMarket FROM TblMarket" _
 & " WHERE FldSysprin='" & AcctSysprin & "'"

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

 

[reubenpatterson]

Same error...thanks for trying though.

 

[PHV]

Same error
Which error ? With which code ?

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

 

[reubenpatterson]

Sorry, "Updating 0 rows" nothing populates in the table.

 

[PHV]

To be sure your SQL is correct, add the following line before the DoCmd call:
Debug.Print FormSubmit2

You may copy the output in the Immediate window (Ctrl+G) and paste it in the SQL view pane of a brand new query to test it.

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

 

[TheAceMan1]

How are ya reubenpatterson . . .

Final syntax appears ok but does the value of [blue]AcctSysprin[/blue] exist in the table? . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[reubenpatterson]

Hi AceMan, it's actually a variable drawn from a form. Here's the complete code:

Dim AcctSysprin As String
AcctSysprin = Forms![FrmEsc4Disp]![AcctNumb]
AcctSysprin = Left$(AcctSysprin, 8)

Dim AcctCitySQL As String
AcctCitySQL = "INSERT INTO TblDateContact"
AcctCitySQL = AcctCitySQL & " (FldCity)"
AcctCitySQL = AcctCitySQL & " SELECT FldMarket"
AcctCitySQL = AcctCitySQL & " FROM TblMarket"
AcctCitySQL = AcctCitySQL & " WHERE FldSysprin = '& AcctSysprin &' "

 

[TheAceMan1]

reubenpatterson . . .

Aside from changing [blue]FormSubmit2[/blue] to [blue]AcctCitySQL[/blue] your syntax is the same and [blue]incorrect![/blue] You should at use the syntax provided by [blue]PHV[/blue].

Now you didn't answer my question:

[blue] . . . does the value of [blue]AcctSysprin[/blue] exist in the table?[/blue]

Specifically does it exist in [blue]TblMarket[/blue]? For instance, if you enterd a new [blue]AcctNumb[/blue] then it doesn't exist in [blue]TblMarket[/blue], hence [blue]"Updating 0 rows"[/blue] is correct!

By chance is [blue]AcctSysprin[/blue] numeric?
If it is drop the two single quotes and try again.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[reubenpatterson]

Ah, sorry 'bout that. No, it doesn't exist in the table, I'm trying to pull it from the form when it's been entered in the AcctNumb field, and store that value and the appropriate city (which is derived from the fldSysprin value.) That's why I thought INSERT was better than UPDATE.

It works in that when it fails due to Null or syntax errors I can see in the debugger that it has picked up the AcctNumb from the field, substring'd it, and is visible in the AcctCitySQL variable.