str query = Update

[Opal]

I'm not sure if I am going about this the
right way, but.....

In access 2003 I have a form where
I want to the user on the submit button
to update the value in one field on
the table. The form is unbound.

I have the following:

Sub SubmitOrg()

Dim HoldTLTeamID As Integer
Dim HoldTLName As Long
Dim HoldTeam As Long
Dim strQuery As String

HoldTLTeamID = Forms!subfrmZoneTMTeam.TLTeamID.Value

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = HoldTLTeamID " & _
"WHERE LstEMP Is Not Null"

CurrentDb.Execute strQuery, dbFailOnError

End Sub

I want the TLTeamID value to equal the
value the user sets on the form for specific
employees noted on the form.

I am getting an error message:

"Too few parameters. Expected 2"

Can I accomplish this with a list box where
the user highlights the names of the employees
where the TLTeamID value needs to be affected?

Can I accomplish this, as well, where the Team Lead's
name is selected from from a combo box?

Any insight / help would be appreciated.

 

[Beetle]

Comments inline;

HoldTLTeamID = Forms!subfrmZoneTMTeam.TLTeamID.Value

Can't be sure, but the name of the form referenced above would
seem to indicate that it is a sub form of another form, so the proper
format for referencing it would be;

Forms!frmTheMainForm!subfrmZoneTMTeam.Form!TLTeamID

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = HoldTLTeamID " & _
"WHERE LstEMP Is Not Null"

The problem with the above is that the reference to the variable
needs to be outside of the quotes;

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & HoldTLTeamID & _
" WHERE LstEMP Is Not Null"

Can I accomplish this with a list box where
the user highlights the names of the employees
where the TLTeamID value needs to be affected?

Yes. You would need to loop through the items selected
in the list box and update each one separately. Example;

Dim varItem As Variant

With Me!lstYourListBox
For Each varItem In .ItemsSelected
strQuery = "Update EmpInfo Set TLTeamID= " & _
.Column(0, varItem) & _
" Where LstEmp Is Not Null"
CurrentDb.Execute strQuery, dbFailOnError
Next varItem
End With

The above example assumes thet the first column of the list box
has the ID value.

Can I accomplish this, as well, where the Team Lead's
name is selected from from a combo box?

Yes, assuming the combo box also contains the ID value.
Example;

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & Me!cboYourCombo & _
" WHERE LstEMP Is Not Null"

HTH

 

[GB]

I have limited experience at access programming, so am only offering the
below information based on observation not experience or proven knowledge.

Perhaps the source of the error is related to the content of the strQuery.
Doesn't there also need to be a semicolon ( at the end of the strQuery
text? At least when I have viewed the SQL for stored queries there always is
a semicolon at the end and if I have used a strQuery like option I have
included the semicolon without error.

 

[Opal]

Beetle

Thank you for your help..... Need to
finesse this just a bit....

For

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & HoldTLTeamID & _
" WHERE LstEMP Is Not Null"

My where statement is not correct.. my fault.
The first column of both the combo and list box
do contain the ID value that needs to be updated
in the EMPInfo table. It is actually the employee
number.

So, to update my Team Lead information my
where statement should be something like

"WHERE TLName =" & EMPNumber

But I am getting a syntax error (missing operator)
in my WHERE expression.....

I would also need to clean this up for the list box

Can you assist?

 

[Opal]

GB,

I am running this query through a module, so the semi-colon
does not apply. That is why I posted my question in this
group as opposed to the query group.

 

[Douglas J. Steele]

Is TLName a text field?

"WHERE TLName ='" & EMPNumber & "'"

Exagerated for clarity, that's

"WHERE TLName = ' " & EMPNumber & " ' "

Note that that will only work if there's no apostrophe in EMPNumber. If
there could be, try

"WHERE TLName =""" & EMPNumber & """"

where that's three double quotes in a row before & EMPNumber &, and four
double quotes in a row after.

 

[Opal]

Its a numeric field

 

[Opal]

Nope, same syntax error....

What am I missing? :-S

 

[Douglas J. Steele]

Do you actually have a value in EMPNumber?

Immediately after you assigned the string to strQuery, put

Debug.Print strQuery

After the code runs, go to the Immediate Window (Ctrl-G). What's typed
there?

 

[John W. Vinson]

Beetle

Thank you for your help..... Need to
finesse this just a bit....

For

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & HoldTLTeamID & _
" WHERE LstEMP Is Not Null"

My where statement is not correct.. my fault.
The first column of both the combo and list box
do contain the ID value that needs to be updated
in the EMPInfo table. It is actually the employee
number.

So, to update my Team Lead information my
where statement should be something like

"WHERE TLName =" & EMPNumber

But I am getting a syntax error (missing operator)
in my WHERE expression.....

I would also need to clean this up for the list box

Can you assist?

If the TLName field is of Text datatype you need some quotemarks around it.
Assuming that EMPNumber will never contain an apostrophe ' character, try

"WHERE TLName = '" & EMPNumber & "'"

Spaced out for readability (don't do it this way!) that's

"WHERE TLName = ' " & EMPNumber & " ' "

so that if EMPNumber is A123 you get

WHERE TLName = 'A123'

 

[Opal]

I'm sorry, Doug..... I'm a newb to this

EMPNumber is the field in the table
for the record that I want to update the
TLTeamID value for.....

I get:

UPDATE EMPInfo SET TLTeamID =1WHERE TLName =0

in the immediate window.

I'm mixing something up, aren't I?

 

[Douglas J. Steele]

You're missing the space between 1 and the keyword WHERE.

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & HoldTLTeamID & _
" WHERE TLName =" & EMPNumber

 

[Opal]

ugh, this is painful....

I am now getting

"Too few parameters. Expected 1"

The immediate window shows:

UPDATE EMPInfo SET TLTeamID = 1 WHERE TLName =0

In this case the TLTeamID is 1

The TLName field if queried in the immediate window is:

?Forms!subfrmTMTeam.TLName
32194

the 32914 is the employee number and this is the record
I want to update.

 

[Opal]

dyslexia...

32194 is the employee number ......

 

[Douglas J. Steele]

It would help immensely if you showed the code you're trying to use.

Based on what you'd posted earlier, I suggested

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & HoldTLTeamID & _
" WHERE TLName =" & EMPNumber

I assumed that you were setting HoldTLTeamID and EMPNumber correctly...

 

[Opal]

This is my complete code:

Sub SubmitOrg()

Dim HoldTLTeamID As Integer
Dim TLName As Long
Dim EMPNumber As Long
Dim strQuery As String

HoldTLTeamID = Forms!subfrmZoneTMTeam.TLTeamID.Value

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & HoldTLTeamID & _
" WHERE TLName = " & EMPNumber

Debug.Print strQuery


CurrentDb.Execute strQuery, dbFailOnError

End Sub

 

[Opal]

Epiphany!

I got it!

I love how you "guys" make me think!

Sub SubmitOrg()

Dim HoldTLTeamID As Integer
Dim HoldTLName As Long
Dim TMNumber As Long
Dim strQuery As String

HoldTLTeamID = Forms!subfrmZoneTMTeam.TLTeamID.Value
HoldTLName = Forms!subfrmZoneTMTeam.TLName.Value

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID = " & HoldTLTeamID & _
" WHERE EMPNumber = " & HoldTLName

Debug.Print strQuery

CurrentDb.Execute strQuery, dbFailOnError

End Sub

 

[Opal]

Okay, so now I want to
do the same thing, but with a list box....

Based on Beetle's earlier reply
and my continued re-working I thought this would do
the trick:

Sub SubmitTMOrg()

Dim varItem As Variant
Dim HoldTLTeamID As Integer
Dim HoldEMPNumber As Long
Dim strQuery As String

HoldTLTeamID = Forms!subfrmZoneTMTeam.TLTeamID.Value
HoldEMPNumber = Forms!subfrmZoneTMTeam.LstTM.Value

With Forms!subfrmZoneTMTeam.LstTM
For Each varItem In .ItemsSelected
strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID= " & HoldTLTeamID & _
.Column(0, varItem) & _
" WHERE EMPNumber = " & HoldEMPNumber

Debug.Print strQuery

CurrentDb.Execute strQuery, dbFailOnError
Next varItem
End With

End Sub

But I get in "Invalid use of Null" error....

:-(

 

[Beetle]

With Forms!subfrmZoneTMTeam.LstTM

For Each varItem In .ItemsSelected
strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID= " & HoldTLTeamID & _
.Column(0, varItem) & _
" WHERE EMPNumber = " & HoldEMPNumber

Debug.Print strQuery

CurrentDb.Execute strQuery, dbFailOnError
Next varItem
End With

In the above, you are trying to use both the variable (HoldTLTeamID)
and the Column reference at the same time. That won't work.
If the first column in the list box contains the value you need, then
you just need to reference that column. You don't need the variable
also.

With Forms!subfrmZoneTMTeam.LstTM
For Each varItem In .ItemsSelected
strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID= " & .Column(0, varItem) & _
" WHERE EMPNumber = " & HoldEMPNumber

Debug.Print strQuery

CurrentDb.Execute strQuery, dbFailOnError
Next varItem
End With

 

[Opal]

Beetle,

Some confusion. The list box contains the
employee number and name. The first column
of the list box is the bound column.

The field in the table I want update is the TLTeamID field
with the value in the HoldTLTeamID

so I think I need to reverse this to something like:

strQuery = "UPDATE EMPInfo " & _
"SET TLTeamID= " & HoldTLTeamID & _
" WHERE EMPNumber = " & .Column(0, varItem)

Does that make sense? I can't test it until tomorrow.