Please can someone check my code 1

[Chance1234]

I have a form which has three combo boxes and one option group on it. from there selections a Update SQL Query is created. it was working fine all until i had to add in the option group now ive lsot my mind.

for it to make more sense here is what the controls are

Combo103. Contains names of trading systems also there is an option all. in the table tblmain, each trading system has its own column and is boolean. (no they cant be consolidated into one column as they can be in more than one system)

Combo99. is just a list of Names of people

combo131 is a status selection which contains text such as Outstanding, Approved, Pending also it has an option all,

Finally frame131 has two options to overwrite records assigned or just those which arent.

bascily the front of the screen looks like this

All Records on System [combo103]
Assign them to [combo99]
Where status is [combo131]

Overright Assigned Records ? Y/N [frame131]

Where iam having trouble is with my brackets in the SQL through different combinations either one too many or one less

here is the code

Please please please help !!!!

Private Sub Command104_DblClick(Cancel As Integer)
Dim StrSql As String
Dim BolAnd As Boolean


StrSql = ""
StrSql = "UPDATE [(a)qryMaster] SET [tblmain].[Assigned To] = " & Combo99.Value & " "

If Combo103.Value = "ALL" Then
StrSql = StrSql
BolAnd = False
Else
StrSql = StrSql & " WHERE ((([tblmain].[" & Combo103.Value & "])=True)"
BolAnd = True
End If


If Combo131.Value = 999 Then
StrSql = StrSql
Else
If BolAnd = True Then
StrSql = StrSql & "AND ((tblMain.Status)=" & Combo131 & " "
Else
StrSql = StrSql & "WHERE ((tblMain.Status)=" & Combo131 & &quot"
End If
End If



If Frame133.Value = 1 Then
StrSql = StrSql & " "
Else
StrSql = StrSql & "AND (([tblmain].[Assigned To]) Is Null)));"

End If
docmd.execute SQL

End Sub

 

[jebry]

Hi!

Look for comments in your code below.




Private Sub Command104_DblClick(Cancel As Integer)
Dim StrSql As String
Dim BolAnd As Boolean


StrSql = ""
StrSql = "UPDATE [(a)qryMaster] SET [tblmain].[Assigned To] = " & Combo99.Value & " "

If Combo103.Value = "ALL" Then
StrSql = StrSql
BolAnd = False
Else
StrSql = StrSql & " WHERE ((([tblmain].[" & Combo103.Value & "])=True)"
BolAnd = True
End If


If Combo131.Value = 999 Then
StrSql = StrSql
Else
If BolAnd = True Then
StrSql = StrSql & "AND ((tblMain.Status)=" & Combo131 & " "

You should have another closing parentheses here and you should have a begining space before the And and before the Where below.


Else
StrSql = StrSql & "WHERE ((tblMain.Status)=" & Combo131 & &quot"
End If
End If



If Frame133.Value = 1 Then
StrSql = StrSql & " "
Else
StrSql = StrSql & "AND (([tblmain].[Assigned To]) Is Null)));"
End If

You will need to take away one of the closing parentheses in the Else statement and also add a space before the And.

docmd.execute SQL

End Sub

hth
Jeff Bridgham

 

[JohnLowell]

You need to check if [tblmain].[Assigned To] and tblMain.Status are string values. If they are, then add single quotes to the SQL.
Try this code:
Private Sub Command104_DblClick(Cancel As Integer)
Dim StrSql As String
Dim BolAnd As Boolean

'StrSql = ""
StrSql = "UPDATE [(a)qryMaster] SET [tblmain].[Assigned To] = " & Combo99.Value & " "

If Combo103.Value = "ALL" Then
'StrSql = StrSql
BolAnd = False
Else
StrSql = StrSql & "WHERE [tblmain].[" & Combo103.Value & "] = True "
BolAnd = True
End If

If Combo131.Value = 999 Then
'StrSql = StrSql
Else
If BolAnd = True Then
StrSql = StrSql & "AND tblMain.Status = " & Combo131 & " "
Else
StrSql = StrSql & "WHERE tblMain.Status =" & Combo131 & " "
End If
End If

If Frame133.Value = 1 Then
StrSql = StrSql & ";"
Else
StrSql = StrSql & "AND [tblmain].[Assigned To] Is Null;"

End If

docmd.runsql strSQL

End Sub

Let me know how this works.

John

 

[Chance1234]

sorry still getting the same problem, but on different combinations now

 

[shaddow]

Please put your table fields type so we can help you... ________

George

 

[jebry]

Hi!

Here's another idea, take away all of the parentheses. It doesn't look like they are necessary anyway!

hth
Jeff Bridgham

 

[Chance1234]

oops another mistake i made in teh first line it should be UPDATE [tblmain] not [(q)qrymaster]


mytables are like this

firstly status just contains ID and Status, ID Is numeric and relates to the status field in tblmain. Status 999 is so the user can select ALL

tblsystem has just one column "system" and is text in that table is the following

System
NTPA
GMI
FX OPS
SDS FB
SDS FBI
GLOSS
ALL

now in tblmain, there are fields called NTPA,GMI,FX OPS, SDS FB, SDS FBI, GLOSS which are all boolean fields, All is to allow the user to select all of them,

at the moment i haev a million post it notes round my desk trying to work this out, my mind has been going down this route,

Private Sub Command104_DblClick(Cancel As Integer)
Dim StrSql As String

Dim ValAll As String
Dim BolAnd As Boolean
Dim strEnd As String

StrSql = ""
strEnd = ""

StrSql = "UPDATE [tblmain] SET [tblmain].[Assigned To] = " & Combo99.Value & " "

If Combo103.Value = "ALL" Then
StrSql = StrSql
BolAnd = False
Else
StrSql = StrSql & " WHERE ((([tblmain].[" & Combo103.Value & "])=True "
BolAnd = True

End If


If Combo131.Value = 999 Then
StrSql = StrSql
Else
If BolAnd = True Then
StrSql = StrSql & " AND ((tblMain.Status)=" & Combo131 & " "
strEnd = strEnd & &quot"
Else
StrSql = StrSql & " WHERE ((tblMain.Status)=" & Combo131 & " "
strEnd = strEnd & &quot"
End If
End If



If Frame133.Value = 1 Then
StrSql = StrSql & strEnd & "; "
Else
StrSql = StrSql & " AND (([tblmain].[Assigned To]) Is Null" & strEnd & ";"

End If

Debug.Print StrSql
End Sub

 

[Chance1234]

i put a screen shot of the tables and the form at

http://public.fotki.com/chance1234/need_help/

 

[shaddow]

So far i see that u could have an unfinnished querry with so manny if clauses...

U are using access or sql?
________

George

 

[Chance1234]

Access, as mentioned the problem im getting is the wrong number of closing brackets, hopefullt the pcitures above make it mroe clearer

 

[shaddow]

So far so god...
If the field [Assigned To] is an string (as i see from your web) u need to put them in ""

StrSql = "UPDATE [tblmain] SET [tblmain].[Assigned To] = """ & Combo99.Value & """

and if you could tell me what is with Combo131...
________

George

 

[Chance1234]

Combo131 is the where status is... the data source for that is tblstatus and bound to column 1 which is the ID. In the code is the If statement if the id is 999 as that is to select all,

Combo103 is the system field with the control source tblsystem.

 

[Chance1234]

combo131 is a number

 

[shaddow]

Combo103? ________

George

 

[shaddow]

Here is an tip...
If u need to add an string into table please
put like this
mystr=mystr&"""Myvalue"""
Is so manny questions about this cuz i'm lost to...
If you (please) could rename the combo an put an better image...
________

George

 

[Chance1234]

combo103 is text, the value is used to select the field in tblmain.

Ill start with when it was simple, tblmain contains infomation about various trades made. what ehy want to do is check these trades to check out for nick leesons and flaming ferraris. theres a lot of trades in there so they decide to put two people working on it "bob" & "Frank" , so in tblmain i add an extra field called assigned. to split the work load they decide that bob will handle all the records where NTPA is True

SO I create my form, i put in Combo99 which links to a table with Bob and Frank in, then i put my button on the form with the code

strsql = "StrSql = "UPDATE [tblmain] SET [tblmain].[Assigned To] = " & Combo99.Value & " "WHERE [NTPA] = True))

no problems

the management all happy, but bob and frank are both overworked, so they bring more people on the team and they want to split the owrk load further so one person handles NTPA< One handles GLoss etc etc so my code now looks like this after adding combo103 which list the systems and my code looks like this

&quot;UPDATE [tblmain] SET [tblmain].[Assigned To] = &quot; & Combo99.Value & &quot; WHERE ((([tblmain].[&quot; & Combo103.Value & &quot;])=True &quot;

after a few weeks some of the trades have been checked some havent, so i add the status field to tblmain, management now what to assisgn all the records which are outstanding to more staff so my code becomes like this

&quot;UPDATE [tblmain] SET [tblmain].[Assigned To] = &quot; & Combo99.Value & &quot; WHERE ((([tblmain].[&quot; & Combo103.Value & &quot;])=True AND WHERE tblMain.Status =&quot; & Combo131 & &quot;;&quot;

All ok so far, now to stop records that have already been assigned i add in the line AND (([tblmain].[Assigned To]) Is Null&quot;

All working fine ! but they want more flexibility! they want to be able assign by status as well as by system, also they want to overwrite some of the assigned records sometimes, and sometimes they jsut want to do the ones which arent so uit has to be one hundred percent flexible.plus they want to be able to chance all systems or al status before i did the frame131 bit it worked then i lost the plot and now drownign in post it notes

 

[mharroff]

It looks to me that in the If Combo103 section you need to add

strEnd = strEnd & &quot&quot;

after

Else
StrSql = StrSql & &quot; WHERE ((([tblmain].[&quot; & Combo103.Value & &quot;])=True &quot;

 

[Chance1234]

ok taking a back step (where have i heard that before ?) without frame131 the code below works perfectly. now what i need to add is the frame131 which cotains the two options Yes and No. if yes then nothing needs to be done StrSQl can stay as strsql but if they chose no, then this line needs to be added to strsql,

&quot; AND (([tblmain].[Assigned To]) Is Null)

this is where iam loosing the plot with the brackets



Private Sub Command104_DblClick(Cancel As Integer)
Dim StrSql As String

Dim ValAll As String
Dim BolAnd As Boolean
Dim strEnd As String

StrSql = &quot;&quot;


StrSql = &quot;UPDATE [tblmain] SET [tblmain].[Assigned To] = &quot; & Combo99.Value & &quot; &quot;

If Combo103.Value = &quot;ALL&quot; Then
StrSql = StrSql
BolAnd = False
Else
StrSql = StrSql & &quot; WHERE ((([tblmain].[&quot; & Combo103.Value & &quot;])=True ))&quot;
BolAnd = True

End If


If Combo131.Value = 999 Then
StrSql = StrSql & &quot;;&quot;
Else
If BolAnd = True Then
StrSql = StrSql & &quot; AND ((tblMain.Status)=&quot; & Combo131 & &quot; &quot;

Else
StrSql = StrSql & &quot; WHERE ((tblMain.Status)=&quot; & Combo131 & &quot; &quot;

End If
End If




Debug.Print StrSql
End Sub

 

[shaddow]

So i'm back again...
If the code u've posted it's ok then try this
----------
Try to put only

If Combo103.Value = &quot;ALL&quot; Then
BolAnd = False
not
If Combo103.Value = &quot;ALL&quot; Then
StrSql = StrSql
BolAnd = False
cuz the strsql is the same...
and maybe that's the problem...
if Combo103.Value = &quot;ALL&quot; then StrSql stays as it is,
so
If Combo131.Value = 999 then u add &quot;;&quot; to StrSql and then StrSql loks like that
&quot;UPDATE [tblmain] SET [tblmain].[Assigned To] = &quot; & Combo99.Value & &quot; &quot;
and that's it your code finished here with an unfinished sql statment

when we add the following line
if frame131.value=&quot;No&quot; then
StrSql=StrSql&&quot; AND (([tblmain].[Assigned To]) Is Null)&quot;

if condition in not true then the SqlStr it's the same
if it's true then StrSql loks like that

&quot;UPDATE [tblmain] SET [tblmain].[Assigned To] = &quot; & Combo99.Value & &quot; &quot;& &quot;AND (([tblmain].[Assigned To]) Is Null)&quot;

with also an unfinished sql statment
so u may have an error in sql statment
u need to be shure u catch all the ways of your program logic and add the final condition at last when u have an valid sql statment this will work just fine...

i've made some modification but not in logic of program...




Private Sub Command104_DblClick(Cancel As Integer)
Dim StrSql As String

Dim ValAll As String
Dim BolAnd As Boolean
Dim strEnd As String

StrSql = &quot;&quot;

StrSql = &quot;UPDATE [tblmain] SET [tblmain].[Assigned To] = &quot; & Combo99.Value & &quot; &quot;

If Combo103.Value = &quot;ALL&quot; Then
BolAnd = False
Else
StrSql = StrSql & &quot; WHERE ((([tblmain].[&quot; & Combo103.Value & &quot;])=True ))&quot;
BolAnd = True
End If

If Combo131.Value = 999 Then
StrSql = StrSql & &quot;;&quot;
Else
If BolAnd = True Then
StrSql = StrSql & &quot; AND ((tblMain.Status)=&quot; & Combo131 & &quot; &quot;
Else
StrSql = StrSql & &quot; WHERE ((tblMain.Status)=&quot; & Combo131 & &quot; &quot;
End If
End If

if frame131.value=&quot;No&quot; then
StrSql=StrSql&&quot; AND (([tblmain].[Assigned To]) Is Null)&quot;

Debug.Print StrSql
End Sub
________

George

 

[Chance1234]

Finally with a bit more tweaking got it working! i kept in the strsl = strsql more for my own logic of knowing what is going on the code i ended up with this

wahay ! now onto the next problem......


Private Sub Command104_DblClick(Cancel As Integer)
Dim StrSql As String

Dim ValAll As String
Dim BolAnd As Boolean
Dim strEnd As String

StrSql = &quot;&quot;


StrSql = &quot;UPDATE [tblmain] SET [tblmain].[Assigned To] = &quot; & Combo99.Value & &quot; &quot;

If Combo103.Value = &quot;ALL&quot; Then
BolAnd = False
StrSql = StrSql
Else
StrSql = StrSql & &quot; WHERE ((([tblmain].[&quot; & Combo103.Value & &quot;])=True ))&quot;
BolAnd = True

End If


If Combo131.Value = 999 Then
StrSql = StrSql
Else
If BolAnd = True Then
StrSql = StrSql & &quot; AND ((tblMain.Status)=&quot; & Combo131 & &quot&quot;

Else
StrSql = StrSql & &quot; WHERE ((tblMain.Status)=&quot; & Combo131 & &quot&quot;

End If
End If


If Frame133.Value = 1 Then
StrSql = StrSql & &quot;;&quot;
Else
StrSql = StrSql & &quot; AND (([tblmain].[Assigned To]) Is Null); &quot;
End If

DoCmd.RunSQL StrSql