More SQL Run Time Issues 1

[carpetbelly]

I'm having one last bit of problems with my SQL (which should once sorted finish my database w00hoo!)

strSQLStaff = "INSERT INTO tblStaff([StaffName], [TeamID], [StaffPayroll]) " & _
              "VALUES (forms!frmStaffInput!txtName,forms!frmStaffInput!cmbTeam,forms!frmStaffInput!txtPayroll);"
              
DoCmd.RunSQL strSQLStaff

StaffID = DLookup("[StaffID]", "qryStaffNameID", "[StaffName] = '" & Name & "'")
txtStaffID.Value = StaffID

strSQLHol = "INSERT INTO tblStaffHolEnt([StaffID], [HolEnt], [CarryFwd], [HolBought]) " & _
            "VALUES (forms!frmStaffInput!txtStaffID!,forms!frmStaffInput!txtHolEnt!," & _
            "forms!frmStaffInput!txtHolCarFwd!,forms!frmStaffInput!txtHolBought!);"

DoCmd.RunSQL strSQLHol

Is what I have at the moment.
Basically, I have variables I'd rather use for the values in the SQL statements so they're easier to read but I couldnt find how to use them. They'd just keep bringing up parameter boxes for values to be entered into them.

Also, the main issue I have is that I seem to be getting a run time 3075 error on the "DoCmd.RunSQL strSQLHol" where it specifies the following

"forms!frmStaffInput!txtStaffID!,"

syntax error (missing operator) in query expression.

Now, the query run above it works fine. The only thing I can think of is that the setting of the value for StaffID but it's done in order so I don't think it's that.

Any help is much appreciated as my mind is blank. Maybe I shouldn't have had that lunch time drink but hey, it's new years!

 

[ZmrAbdulla]

May be the exclamation("!") marks at the end(s) are the creating problem. Remove them.

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[carpetbelly]

Yep, drink and coding prob not the best idea... lol

 

[carpetbelly]

I know this is now working but I'd like to make my code a bit nicer. Is there anyway I could use variables declared in the code rather than the full path to the value of the variable in the SQL code.

So for example rather than using

forms!frmStaffInput!txtStaffID

I could use

dim StaffID as integer

StaffID = txtStaffID.value

And then use the variable 'StaffID' in the SQL to make it neater.

 

[ZmrAbdulla]

Yes you can.. and it is the betterway of doing..the basic of OOP concept. You have a chance to verify the value entered is correct(numeric, date, text etc..)
You will replace the "forms!frmStaffInput!txtStaffID" part to "StaffID" in your code.


________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[carpetbelly]

Yeah, I've tried that and all I get is a prompt up to enter the parameter value for 'Name' etc. I've also tried doing it with double and single quotes to show it's outside of the sql string without any luck. It's quite baffling.

 

[PHV]

...
Dim StaffID As Integer
StaffID = Me!txtStaffID.Value
strSQLHol = "INSERT INTO tblStaffHolEnt([StaffID], [HolEnt], [CarryFwd], [HolBought]) " & _
            "VALUES ([!]" & StaffID & "[/!],Forms!frmStaffInput!txtHolEnt," & _
            "Forms!frmStaffInput!txtHolCarFwd,Forms!frmStaffInput!txtHolBought!);"
...

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

 

[CaptainD]

Might need to add ' marks

strSQLHol = "INSERT INTO tblStaffHolEnt([StaffID], [HolEnt], [CarryFwd], [HolBought]) " & _
            "VALUES ([COLOR=red]'[/color]" & StaffID & "[COLOR=red]'[/color],Forms!frmStaffInput!txtHolEnt," & _
            "Forms!frmStaffInput!txtHolCarFwd,Forms!frmStaffInput!txtHolBought!);"

 

[CaptainD]

Disreguard, it's an integer value