Execute question

[Rhea]

I am trying to update a table. I have this statement:

dbsSecurity.Execute "UPDATE TechSecurity SET Mailbox =
Forms!AddMailboxes!strMailbox WHERE Techcode = Forms!AddMailboxes!intTechcode"

but I get this error: Runtime error 3061 too few paramters expected 2

what is wrong with the statement?

Thanks!

 

[dymondjack]

The Execute method will not call on access expression service (ES gets the
values from expressions like Forms!... or Me.ctlWhatever).

You probably want to build an SQL string first, inserting the actual values,
and then execute the completed statement. Be careful of quotes when building
an SQL string.



Dim strSQL As String
strSQL = "UPDATE TechSecurity SET Mailbox = " &
Forms!AddMailboxes!strMailbox _
& "WHERE Techcode = " & Forms!AddMailboxes!intTechcode

dbsSecurity.Execute strSQL


hth



--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[fredg]

I am trying to update a table. I have this statement:

dbsSecurity.Execute "UPDATE TechSecurity SET Mailbox =
Forms!AddMailboxes!strMailbox WHERE Techcode = Forms!AddMailboxes!intTechcode"

but I get this error: Runtime error 3061 too few paramters expected 2

what is wrong with the statement?

Thanks!

What datatypes are MailBox and TechCode?
Try concatenating the values into the statement.
Assuming both fields are Text datatypes, try:

dbsSecurity.Execute "UPDATE TechSecurity SET TechSecurity.Mailbox = '"
& Forms!AddMailboxes!strMailbox & "' WHERE TechSecurity.Techcode = '"
& Forms!AddMailboxes!intTechcode & "'", dbFailOnError

The syntax changes if the datatype is number or date.
Don't forget to add error handling.

 

[Rhea]

Thanks. Now I get an error that access can't find the variable strMailbox. I
have it defined, not sure what it is complaining about now. Here is the code
I have:

Private Sub Command1_Click()

Dim dbsSecurity As Database
'Dim rstTechSecurity As Recordset
Dim intTechcode As Integer
Dim strMailbox As String
Dim strSQL As String
Dim FileNumber As Integer
Dim sFile As String
Dim strLine As String
Dim MyPos

Set dbsSecurity = OpenDatabase("Security.mdb")

FileNumber = FreeFile
Open "C:\mailboxestest.txt" For Input As #FileNumber 'open the file

intTechcode = 1
Do Until EOF(FileNumber) 'loop through each line in the file
Line Input #FileNumber, strLine 'store the line as a variable
MyPos = InStr(1, strLine, ",", 1)
If MyPos = 3 Then
intTechcode = Val(Left(strLine, 2))
ElseIf MyPos = 4 Then
intTechcode = Val(Left(strLine, 3))
Else
intTechcode = Val(Left(strLine, 4))
End If
strMailbox = Mid([strLine], MyPos + 1, Len(strLine) - MyPos)
strSQL = "UPDATE TechSecurity SET Mailbox = " &
Forms!AddMailboxes!strMailbox & "WHERE Techcode = " &
Forms!AddMailboxes!intTechcode
dbsSecurity.Execute strSQL
Loop
End Sub

The field techcode is a number and the variable is an integer. What do I
have to do to handle that? strMailbox is a string and the field is also a
string.

Thanks.

 

[dymondjack]

I'm not exactly sure what you are using strMailBox for... you have it
declared as a variable, with a value being added to it, but I don't see where
you use that variable for anything after it's value is assigned.

You apparently also have a control named strMailBox
(Forms!AddMailboxes!strMailbox). VBA may be confusing the control with the
variable. You may want to change your variable name to something that is not
the same as the control.

I'm not exactly sure that this is where the problem lies, but that's where
I'd start.

hth
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

Thanks. Now I get an error that access can't find the variable strMailbox. I
have it defined, not sure what it is complaining about now. Here is the code
I have:

Private Sub Command1_Click()

Dim dbsSecurity As Database
'Dim rstTechSecurity As Recordset
Dim intTechcode As Integer
Dim strMailbox As String
Dim strSQL As String
Dim FileNumber As Integer
Dim sFile As String
Dim strLine As String
Dim MyPos

Set dbsSecurity = OpenDatabase("Security.mdb")

FileNumber = FreeFile
Open "C:\mailboxestest.txt" For Input As #FileNumber 'open the file

intTechcode = 1
Do Until EOF(FileNumber) 'loop through each line in the file
Line Input #FileNumber, strLine 'store the line as a variable
MyPos = InStr(1, strLine, ",", 1)
If MyPos = 3 Then
intTechcode = Val(Left(strLine, 2))
ElseIf MyPos = 4 Then
intTechcode = Val(Left(strLine, 3))
Else
intTechcode = Val(Left(strLine, 4))
End If
strMailbox = Mid([strLine], MyPos + 1, Len(strLine) - MyPos)
strSQL = "UPDATE TechSecurity SET Mailbox = " &
Forms!AddMailboxes!strMailbox & "WHERE Techcode = " &
Forms!AddMailboxes!intTechcode
dbsSecurity.Execute strSQL
Loop
End Sub

The field techcode is a number and the variable is an integer. What do I
have to do to handle that? strMailbox is a string and the field is also a
string.

Thanks.

The Execute method will not call on access expression service (ES gets the
values from expressions like Forms!... or Me.ctlWhatever).

You probably want to build an SQL string first, inserting the actual values,
and then execute the completed statement. Be careful of quotes when building
an SQL string.



Dim strSQL As String
strSQL = "UPDATE TechSecurity SET Mailbox = " &
Forms!AddMailboxes!strMailbox _
& "WHERE Techcode = " & Forms!AddMailboxes!intTechcode

dbsSecurity.Execute strSQL


hth



--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Rhea]

I have found some help in the 'help'. strMailbox adn intTechcode are
variables i want to use in the SQL string. I discovered I don't need to
reference the way I was. So I changed the SQL string to this:

strSQL = "UPDATE TechSecurity SET Mailbox = " & strMailbox & "WHERE Techcode
= " & intTechcode

Now I get an syntax error. The field techcode is a number field and the
field mailbox is a string field. I am reading a text file line by line to
update records in a table. I set the variables to the values I want and then
want to update the mailbox field with the value in strMailbox where the
techcode is = to the techcode in the variable (intTechcode). How do I write
the sqwl statement to do that? Thanks.

I'm not exactly sure what you are using strMailBox for... you have it
declared as a variable, with a value being added to it, but I don't see where
you use that variable for anything after it's value is assigned.

You apparently also have a control named strMailBox
(Forms!AddMailboxes!strMailbox). VBA may be confusing the control with the
variable. You may want to change your variable name to something that is not
the same as the control.

I'm not exactly sure that this is where the problem lies, but that's where
I'd start.

hth
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

Thanks. Now I get an error that access can't find the variable strMailbox. I
have it defined, not sure what it is complaining about now. Here is the code
I have:

Private Sub Command1_Click()

Dim dbsSecurity As Database
'Dim rstTechSecurity As Recordset
Dim intTechcode As Integer
Dim strMailbox As String
Dim strSQL As String
Dim FileNumber As Integer
Dim sFile As String
Dim strLine As String
Dim MyPos

Set dbsSecurity = OpenDatabase("Security.mdb")

FileNumber = FreeFile
Open "C:\mailboxestest.txt" For Input As #FileNumber 'open the file

intTechcode = 1
Do Until EOF(FileNumber) 'loop through each line in the file
Line Input #FileNumber, strLine 'store the line as a variable
MyPos = InStr(1, strLine, ",", 1)
If MyPos = 3 Then
intTechcode = Val(Left(strLine, 2))
ElseIf MyPos = 4 Then
intTechcode = Val(Left(strLine, 3))
Else
intTechcode = Val(Left(strLine, 4))
End If
strMailbox = Mid([strLine], MyPos + 1, Len(strLine) - MyPos)
strSQL = "UPDATE TechSecurity SET Mailbox = " &
Forms!AddMailboxes!strMailbox & "WHERE Techcode = " &
Forms!AddMailboxes!intTechcode
dbsSecurity.Execute strSQL
Loop
End Sub

The field techcode is a number and the variable is an integer. What do I
have to do to handle that? strMailbox is a string and the field is also a
string.

Thanks.

The Execute method will not call on access expression service (ES gets the
values from expressions like Forms!... or Me.ctlWhatever).

You probably want to build an SQL string first, inserting the actual values,
and then execute the completed statement. Be careful of quotes when building
an SQL string.



Dim strSQL As String
strSQL = "UPDATE TechSecurity SET Mailbox = " &
Forms!AddMailboxes!strMailbox _
& "WHERE Techcode = " & Forms!AddMailboxes!intTechcode

dbsSecurity.Execute strSQL


hth



--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill


:

I am trying to update a table. I have this statement:

dbsSecurity.Execute "UPDATE TechSecurity SET Mailbox =
Forms!AddMailboxes!strMailbox WHERE Techcode = Forms!AddMailboxes!intTechcode"

but I get this error: Runtime error 3061 too few paramters expected 2

what is wrong with the statement?

Thanks!