AddNew Method -- Need Help 1

[sebell]

I am trying to .addnew to my db. Brief History: I have created code that appends fields to a table (which the user types in what they want). I want to append records to these fields, but since I obviously don't know the names of the fields prior to writing the code I was setting it up as follows:

Set dbAddPhases = CurrentDb
Set rstAddPhases = dbAddPhases.OpenRecordset("Timecard Options&quot
With rstAddPhases
.addnew
If IsNull(Me![txtPhase1]) Then
'do nothing
Else
FieldName = "Cost Code_" & Me![txtCustomerJob]
--------->![FieldName] = Me![txtPhase1]
End If
.Update
End With
This, however, is not working & can't find item in table. How can I append to the field using a general variable that I define (such as I was trying to do w/'FieldName'.
Thank you in advance for any help you may offer me.

 

[jrbarnett]

Hello

Use an Insert query that you build up in a string instead of the Recordset:

strQuery = "Insert into [Timecard Options] ([Cost code_" & Me!txtCustomerJob & "]) Values (" & Me!txtPhase1 & "'&quot

DoCmd.RunSQL strQuery

You might want to DoCmd.SetWarnings False beforehand and DoCmd.SetWarnings True afterwards to stop the Access confirmation messages appearing.

John

 

[sebell]

Hi John,
I am kind of confused on how to do this exactly,
Do I dim strQuery as string?

If IsNull(Me![txtPhase1]) Then
'do nothing
Else
Field = "Cost Code_" & Me![txtCustomerJob]
' ![Field] = Me![txtPhase1]
strQuery = ("Insert into [Timecard Options] ([Cost code_" & Me!txtCustomerJob & "]) Values (" & Me!txtPhase1 & "'&quot
DoCmd.RunSQL strQuery
End If

I tried this & I got an error message: "Syntax error (missing operator) in query expression 'Phase 1'."

Thanks,
Sherry

 

[jrbarnett]

Hi

Yes - strQuery should be declared as a string.
I have just noticed a slight error in my previous post. It should read:

strQuery = ("Insert into [Timecard Options] ([Cost code_" & Me!txtCustomerJob & "]) Values (" & Me!txtPhase1 & "')"

John

 

[sebell]

Hi John,
I tried this and it debugs on the docmd w/error message: -- 'Syntax error in string in query expression '100".' --
(I used '100 misc' as the end user input just to work with)

Thank you,
Sherry

 

[jrbarnett]

Hi

Sorry, I have just spotted the mistake myself:
put an apostrophe just after the open bracket, so it reads:

("Insert into [Timecard Options] ([Cost code_" & Me!txtCustomerJob & "]) Values ('" & Me!txtPhase1 & "')"

This should fix it. Big apologies for the mistake earlier.

John

 

[sebell]

You are genius. Thank you, this is exactly what I was looking for. I appreciate your help with this. Have a great new year and thank you.