Solved: Insert SQL with Yes/No Box

[FrymanTCU]

So I have a form with checkboxes: ckbxQuality and ckbxAuditor. I want to add a new record regardless but if either of these check box are selected I want to change the Yes/No MultiRole field in my uderlying table, Users, to 'yes'. But I am having problems getting it to update that field, I get a message box asking for the strMultiRole or it just runs and does not update. I am not having problems with the rest of the data populating but this one field. Also, step 2 which I haven't started, if it is a MultiRole user then there is a second table I have to update but that we can cross that bridge when we reach it. Treavor, I know your out there, what do you think? Isn't SQL fun! :banghead:

Here's my code so far:
Private Sub cmdOK_Click()
Dim strUser As String
Dim strMultiRole As String
Dim mySQL As String

strMultiRole = "No"
strUser = Nz(DLookup("UserID", "Users", "UserID='" & txtboxUserName.Value & "'"), "nouser")
If strUser = "nouser" Then

If Not IsNull(Me.ckbxQuality) Then
strMultiRole = "Yes"
End If
If Not IsNull(Me.ckbxAuditor) Then
strMultiRole = "Yes"
End If

mySQL = "Insert InTo [Users] (UserID, FirstName, LastName, RoleID, MultiRole, Password, DeptName, MSID)" _
& "VALUES ('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','" & [Forms]![AddRTSUser]![txtFirstName].Value & "'," _
& "'" & [Forms]![AddRTSUser]![txtLastName].Value & "','2', strMultiRole , '" & [Forms]![AddRTSUser]![txtboxPassword].Value & "'," _
& "'" & [Forms]![AddRTSUser]![ComboDeptName].Value & "','" & [Forms]![AddRTSUser]![txtMSID].Value & "')"

DoCmd.RunSQL mySQL
MsgBox "User has been Added"
DoEvents
DoCmd.Close acForm, "AddRTSUser"
DoCmd.OpenForm "Manage Users"
Else
MsgBox "Username already exists"
End If
End Sub


Any help is appreciated, Thanks in advance.

[Trevor]

try an Insert Query (assuming thoes checkboxes are on the same form as the textboxes the users input data into the table with.
And if these check boxes are on their own seperate form use an update table query (google for exact syntax), to give you an idea its update tablename.field , where fieldnameoftable = fieldnameonform, Set, field name to "x";"
(fieldname, fieldname,....)" _
& " Values ("Yes","No",.....);"

(field name, and yes, no are respective to each other)
If you don't want to alarm the user turn the warnings off by above your Docmd.runsql

Docmd.Setwarning false

And under values

DoCmd.Setwarning true

it would look like this
[VBA]
Docmd.Setwarnings False
Docmd.Runsql "Insert InTo [TableName] (fieldName, fieldname,..)" _
& " Values ("yes","No",...):"
DoCmd.Setwarning true
[VBA]

[akn112]

just a small suggestion for cleanup


If Not IsNull(Me.ckbxQuality) Then
strMultiRole = "Yes"
End If
If Not IsNull(Me.ckbxAuditor) Then
strMultiRole = "Yes"
End If


can be replaced with


strMultiRole = iif(ckbxAuditor=true or ckbxQuality=true,"Yes","")

[FrymanTCU]

akn211, you're really going to laugh at the size of a mess I made. I like the way you tried to clean up my code but I think this guy has gone beyond repair. If you can see any shortcuts I would be more than happy to try them. Let me remind you I am a beginner but it is functioning so I'm not too concerned. I think I must have a missing reference or something because I have a problem using a string inside of a string but then again I have no clue what I'm talking about. But for a good laugh look below, and don't hate at least I'm trying! :dunno

Private Sub cmdOK_Click()
Dim strUser As String
Dim strMultiRole As String
Dim strQuality As String
Dim strAuditor As String
Dim mySQL As String
Dim QualitySQL As String
Dim AuditorSQL As String
Dim ProcessorSQL As String
Dim MyVar As Byte

strMultiRole = "No"
strUser = Nz(DLookup("UserID", "Users", "UserID='" & txtboxUserName.Value & "'"), "nouser")
If strUser = "nouser" Then

If Not IsNull(Me.ckbxQuality) Then
strMultiRole = "Yes"
strQuality = "true"
If strQuality = "true" Then
QualitySQL = "Insert InTo [UserRoles] (UserID,RoleID) Values('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','3')"
DoCmd.RunSQL QualitySQL
End If
End If
If Not IsNull(Me.ckbxAuditor) Then
strMultiRole = "Yes"
strAuditor = "true"
If strAuditor = "true" Then
AuditorSQL = "Insert InTo [UserRoles] (UserID,RoleID) Values('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','4')"
DoCmd.RunSQL AuditorSQL
End If
End If
If strMultiRole = "Yes" Then
mySQL = "Insert InTo [Users] (UserID, FirstName, LastName, RoleID, MultiRole, Password, DeptName, MSID)"
mySQL = mySQL & "VALUES ('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','" & [Forms]![AddRTSUser]![txtFirstName].Value & "',"
mySQL = mySQL & "'" & [Forms]![AddRTSUser]![txtLastName].Value & "','2', yes , '" & [Forms]![AddRTSUser]![txtboxPassword].Value & "',"
mySQL = mySQL & "'" & [Forms]![AddRTSUser]![ComboDeptName].Value & "','" & [Forms]![AddRTSUser]![txtMSID].Value & "')"
ProcessorSQL = "Insert InTo [UserRoles] (UserID,RoleID) Values('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','2')"
DoCmd.RunSQL ProcessorSQL
Else
mySQL = "Insert InTo [Users] (UserID, FirstName, LastName, RoleID, MultiRole, Password, DeptName, MSID)"
mySQL = mySQL & "VALUES ('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','" & [Forms]![AddRTSUser]![txtFirstName].Value & "',"
mySQL = mySQL & "'" & [Forms]![AddRTSUser]![txtLastName].Value & "','2', no , '" & [Forms]![AddRTSUser]![txtboxPassword].Value & "',"
mySQL = mySQL & "'" & [Forms]![AddRTSUser]![ComboDeptName].Value & "','" & [Forms]![AddRTSUser]![txtMSID].Value & "')"
End If

DoCmd.RunSQL mySQL
MyVar = MsgBox(("User has been Added, Would you like to add another User?"), vbYesNo + vbQuestion, "RTS User Access")
If MyVar = 6 Then
DoEvents
DoCmd.Close acForm, "AddRTSUser"
DoCmd.OpenForm "AddRTSUSer"
Else
DoEvents
DoCmd.Close acForm, "AddRTSUser"
DoCmd.OpenForm "Manage Users"
End If
Else
MsgBox "Username already exists"
End If
End Sub

[Trevor]

FryMan, I changed your docmdRunsql from strmuliuser = yes as an example

DoCmd.RunSQL ProcessorSQL ("Insert InTo [Users] (UserID, FirstName, LastName, RoleID, MultiRole, Password, DeptName, MSID)" & _ & "VALUES ('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','" & [Forms]![AddRTSUser]![txtFirstName].Value & "'," mySQL = mySQL & "'" & [Forms]![AddRTSUser]![txtLastName].Value & "','2', yes , '" & [Forms]![AddRTSUser]![txtboxPassword].Value & "'," & "'" & [Forms]![AddRTSUser]![ComboDeptName].Value & "','" & [Forms]![AddRTSUser]![txtMSID].Value & "')" ProcessorSQL = "Insert InTo [UserRoles] (UserID,RoleID) Values('" & [Forms]![AddRTSUser]![txtboxUserName].Value & "','2')" )

setting each line of a sql string to a variable never worked for me, so I'd sugesst try what I sugessted above and see if that helps

[Carl A]

Yes/No Fields should be avoided. They cause more problems then they are worth. If you doubt then google it and see what the experts say.
Just my :2p:

[Trevor]

Carl is right yes no fields can cause alot of trouble but if you want to deal with the headach go for it, but it can be done I have yes/no field in one of my forms but I had to write about 8 lines of corde to deal with it and pass the value to a string variable

[FrymanTCU]

Thanks I did not know that, I will try to avoid them in the future. In this case I did not design the original database, it is something that has been in place well before I started in my position. I guess my sorry excuse for a code will have to make do, at least all my error messages are gone!

[Trevor]

Fryman, just curious what did you finaly use to fix it?
I assume Carl when he was talking about yes/no fields he was referring to checkboxes, not combo, because I have only experienced problems with yes/no checkboxes.

[Carl A]

Here is a short description of what I mean by using yes/no fields.
http://allenbrowne.com/NoYesNo.html
and
http://allenbrowne.com/bug-14.html

[FrymanTCU]

Trevor, I just left the long mess of a script I have above. I have too many projects going at once to spend all day making that code all neat and tidy. I wish I was a coding ninja like some of the regulars on here but I am not there yet.

Carl, thanks for all the info you are a Mr Miague(sp?) of the VBA fourms! Your help is much appreciated.

Sincerely,
Daniel Sun
aka Rich

[Trevor]

thanks..