same insert into problem

[Bongard]

Hi I am having a problem trying to get the data entered into a form
copied over into a second table. I would like to do that exact same
thing that I did in another from within the report only from a
different form into a different table and Access doesn't seem to be
liking my formula.

This is the old formula in my report that is working: This works,
taking fields [LN1] and [AMOUNT1] from the current form and
inserting(ior copying) them into the fields [Loan Number], and [Amount]
on the transactions table.

'This is my model from frm LTLT
'If [LN1] <> 0 Then
'strsql = "Insert Into [tbl_Transactions]([Loan Number], Amount)
Values(" & [LN1] & "," & [AMOUNT 1] & ")"
'CurrentDb.Execute strsql, dbFailOnError
'End If

This is my new formula: I am trying something similar by trying to
insert [Loan Number] and [LoanNotes] from the current form into the
[Loan Number] and [Notes] fields on the transactions table.

If [Loan Number] <> 0 Then
strsql = "Insert Into [tbl_Transactions]([Loan Number], [Notes])
Values(" & [Loan Number] & "," & [LoanNotes] & ")"
CurrentDb.Execute strsql, dbFailOnError
End If

For some reason this new formula is not working the error it is giving
me is
Run-Time error '3075':
Syntax Error (missing operator) in query expression 'Displays here
whatever note I input'

Then when I click on debut it highlights the last portion of my
formula, the CurrentDb.Execute strsql, dbFailOnError part.

If anyone could help me with this I would greatly appreciate it as I am
about 1 report away from being done with a month long project!

 

[Dirk Goldgar]

Hi I am having a problem trying to get the data entered into a form
copied over into a second table. I would like to do that exact same
thing that I did in another from within the report only from a
different form into a different table and Access doesn't seem to be
liking my formula.

This is the old formula in my report that is working: This works,
taking fields [LN1] and [AMOUNT1] from the current form and
inserting(ior copying) them into the fields [Loan Number], and
[Amount] on the transactions table.

'This is my model from frm LTLT
'If [LN1] <> 0 Then
'strsql = "Insert Into [tbl_Transactions]([Loan Number], Amount)
Values(" & [LN1] & "," & [AMOUNT 1] & ")"
'CurrentDb.Execute strsql, dbFailOnError
'End If

This is my new formula: I am trying something similar by trying to
insert [Loan Number] and [LoanNotes] from the current form into the
[Loan Number] and [Notes] fields on the transactions table.

If [Loan Number] <> 0 Then
strsql = "Insert Into [tbl_Transactions]([Loan Number], [Notes])
Values(" & [Loan Number] & "," & [LoanNotes] & ")"
CurrentDb.Execute strsql, dbFailOnError
End If

For some reason this new formula is not working the error it is giving
me is
Run-Time error '3075':
Syntax Error (missing operator) in query expression 'Displays here
whatever note I input'

Then when I click on debut it highlights the last portion of my
formula, the CurrentDb.Execute strsql, dbFailOnError part.

If anyone could help me with this I would greatly appreciate it as I
am about 1 report away from being done with a month long project!

My guess is that the Notes field is a text or memo field. If that's
correct, you need to enclose the value of [LoanNotes] in quotes. Since
a field for "notes" might well contain whatever quote character I use
(single ' or double "), I'll suggest code that is "quote-safe":

strsql = _
"Insert Into [tbl_Transactions] ([Loan Number], [Notes]) " & _
"Values(" & [Loan Number] & "," & _
Chr(34) & _
Replace([LoanNotes], Chr(34), Chr(34) & Chr(34)) & _
Chr(34) & ")"

If I'm wrong in my guess about the Notes field, though, all bets are
off.

 

[Jeff L]

I would say that you are getting an error because Loan Notes is a text
value. When you insert a text value, you need single quotes around the
value, no quotes for numeric values.

Values(" & [Loan Number] & ",'" & [LoanNotes] & "')"

 

[Bongard]

Thanks both of you for responding so quickly. I tried both methods and
I got close to the same error claiming that it couldn't find the field
and I have gone through a number of times making sure the fields in the
tables and Form are named correctly. I now have:

If [LN] <> 0 Then
strsql = "Insert Into [tbl_Transactions] ([Loan Number], [Notes]) " &
"Values(" & [Loan Number] & "," & Chr(34) & Replace([LoanNotes],
Chr(34), Chr(34) & Chr(34)) & Chr(34) & ")"
CurrentDb.Execute strsql, dbFailOnError
End If

And it is giving me the error
Microsoft Access can't find the field 'I' referred to in your
expression.

I don't know what the deal is, I am lost now.

Please help!

Thanks,
brian

 

[Dirk Goldgar]

Thanks both of you for responding so quickly. I tried both methods and
I got close to the same error claiming that it couldn't find the field
and I have gone through a number of times making sure the fields in
the tables and Form are named correctly. I now have:

If [LN] <> 0 Then
strsql = "Insert Into [tbl_Transactions] ([Loan Number], [Notes]) " &
"Values(" & [Loan Number] & "," & Chr(34) & Replace([LoanNotes],
Chr(34), Chr(34) & Chr(34)) & Chr(34) & ")"
CurrentDb.Execute strsql, dbFailOnError
End If

And it is giving me the error
Microsoft Access can't find the field 'I' referred to in your
expression.

I don't know what the deal is, I am lost now.

Please help!

Thanks,
brian

I can't see why you would get that message with that SQL statement. Are
you sure the error is being raised on the CurrentDb.Execute line? If it
is, please set a breakpoint there, run through the code, and when it
stops at that point, check the value of strsql. You can enter

Debug.Print strsql

in the Immediate Window to get the contents of the variable printed out.
Then, if the reason for the problem isn't obvious, please copy and paste
it into a reply.

 

[Bongard]

The new error, when I click debug, does not point there anymore it now
points to (or makes yellow) the line with the insert into statement.

strsql = "Insert Into [tbl_Transactions] ([Loan Number], [Notes]) " &
"Values(" & [Loan Number] & "," & Chr(34) & Replace([LoanNotes],
Chr(34), Chr(34) & Chr(34)) & Chr(34) & ")"

And if you want me to run through the SQL or whatever you would just
need to give me a little more detailed instruction as I am not quite
sure how to do that.

Thanks Dirk!

-brian

 

[Dirk Goldgar]

The new error, when I click debug, does not point there anymore it now
points to (or makes yellow) the line with the insert into statement.

strsql = "Insert Into [tbl_Transactions] ([Loan Number], [Notes]) " &
"Values(" & [Loan Number] & "," & Chr(34) & Replace([LoanNotes],
Chr(34), Chr(34) & Chr(34)) & Chr(34) & ")"

Was that statement originally all on one line, or was it broken onto
three lines the way I see it in your post? If you had it on three lines
in your original code, you need to include the line continuation
characters, as I originally posted it. Did you try copying and pasting
exactly this:

strsql = _
"Insert Into [tbl_Transactions] ([Loan Number], [Notes]) " & _
"Values(" & [Loan Number] & "," & _
Chr(34) & _
Replace([LoanNotes], Chr(34), Chr(34) & Chr(34)) & _
Chr(34) & ")"

into your code?

 

[Bongard]

I did take out the breaks and include it all on one line, but I tried
pasting this in as well and I am still gettting the same message about
MS Access can't find the field 'I' referred to in your expression and
now highlights the new text that I had pasted into the after update
event.

I'm still puzzled

 

[Dirk Goldgar]

I did take out the breaks and include it all on one line, but I tried
pasting this in as well and I am still gettting the same message about
MS Access can't find the field 'I' referred to in your expression and
now highlights the new text that I had pasted into the after update
event.

I'm still puzzled

So am I, as that code compiles and executes fine for me. Do you have
the Name AutoCorrect option turned on, by any chance? If so, I wonder
if that's causing problems, as it sometimes does. Try clicking Tools ->
Options..., go to the General tab, and uncheck "Perform name
AutoCorrect".

If that doesn't fix the problem, the only thing I can think of is that
one of those controls you pick up values from is a calculated control
with an invalid controlsource expression.

 

[Bongard]

Dirk I think I got it. I just quit outta that report and started a new
one from scratch. The first and only change that I made was to add your
formula to the after update event and it seems to be working just fine.

thanks so much!