Please Help me with Check Box issues

[Kaybaj]

i have a continous form with a command button which opens a password form when you are validated rightly the "post button" on the password form should do the following
1) insert records whose checked boxes are checked into another table payment
2) delete the same checked and inserted record from the table i used as a temporary storage
3) leave the records whose checked boxes are not checked in my temporary table

this is the code i've so far

Private Sub CMDLOGIN_Click()
On Error GoTo Err_CMDLOGIN_Click
Dim STDOCNAME As String
Dim THEQUERY As String
Dim THEQUERY2 As String
Dim Msg, Style, Title, Response, MyString
Dim strMsg As String 'MsgBox message.
Msg = "Authorisation Cancelled" ' Define message.
Style = vbOKOnly
Title = "mindBASE" ' Define title.
THEQUERY = "INSERT INTO [NEW PAYMENTS3] SELECT * FROM [NEW PAYMENTS] WHERE TAG = -1"
THEQUERY2 = "delete from [NEW PAYMENTS] WHERE [TAG] = -1"
'Check to see if data is entered into the UserName combo box
If IsNull(Me.Combo22) Or Me.Combo22 = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.Combo22.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.Text24) Or Me.Text24 = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Text24.SetFocus
Exit Sub
End If
DoCmd.Close acForm, "PASSWORD DBA", acSaveNo
If Me.Text24.Value = DLookup("PASSWORD", "AUTHORISATION", _
"[USERID]=" & Me.Combo22.Value) Then
USERID = Me.Combo22.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "AUTHORISATION PAYMENT", acSaveNo
DoCmd.SetWarnings True
DoCmd.RunSQL THEQUERY
DoCmd.SetWarnings False
DoCmd.RunSQL THEQUERY2
DoCmd.RunMacro "CLOSE PAYMENT TEMP"
DoCmd.OpenForm "PAYMENT FORM TEMP"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"mindBASE!"
Me.Text24.SetFocus
End If
Exit_CMDLOGIN_Click:
Exit Sub
Err_CMDLOGIN_Click:
Response = MsgBox(Msg, Style, Title)
End Sub

 

[DCrake]

Ok I get that but where does the Check box issue come into it and what is the issue?

 

[Kaybaj]

It generates error message saying TAG cannot be referenced

 

[Kaybaj]

error on the insert to statement that it contains an unknownfield 'TAG'

 

[Kaybaj]

please i need help on this issue, its been a pain in my spine any idea on this will be highly appreciated cos i'm hooked thanks bro

 

[DCrake]

Tag is an Access reserved word, and as such may be the base of the issue. Do you actually have a field called tag. If so you need to put it in [] at least.

 

[Kaybaj]

thanks for that it actually dis some work, but not as i want
1) if you check one it might say you are appending 2 or 0 records
2) if you check two it might say you are appending 1 record etc.

so its not actually following the check option

thanks for your help

 

[DCrake]

THEQUERY2 = "delete from [NEW PAYMENTS] WHERE [TAG] = -1"

This line is wrong

"Delete * from [NEW PAYMENTS] WHERE [TAG] = -1"

 

[JamesMcS]

I did mention that in one of the other 2 threads you have open on this subject kaybaj, also that it's best to only have one thread open per question.

 

[Kaybaj]

it was a mistake i admit that.

 

[Kaybaj]

even with this
THEQUERY2 = "delete from [NEW PAYMENTS] WHERE [TAG] = -1"
the query still executes.
it is the check box that i cant seem to get to do what i want i.e when i check it the code should do this
THEQUERY = "INSERT INTO [NEW PAYMENTS3] SELECT * FROM [NEW PAYMENTS] WHERE TAG = -1"
but what it does is in line with what i check.

thanks for your attention and time

 

[JamesMcS]

Have you got the code in the afterupdate property of the check box? Usually what I do is something like "If checkbox value is true then execute code, if not, do something else/nothing"

 

[Kaybaj]

how can i make one master check box check all the other check boxes because i have a form with several boxes to be checked.
thanks

 

[JamesMcS]

One check box can be made to check/uncheck others in VBA - in the on click (or after update - i forget which is best, I think on click though) use a select case or an if:

select case *insert check box name here*

Case 0 'False

[checkbox1 name].value=0
....

case -1 'True

[checkbox1 name].value=-1

end select

 

[Kaybaj]

i got that but the select case is giving me error

 

[JamesMcS]

What kind of error? Try and be a wee bit more descriptive so we can help....

 

[Kaybaj]

sorry my mistake i got it now
thanks for your help

 

[JamesMcS]

Excellent, glad to help.

 

[Kaybaj]

but please why is it just selecting only one of the checkboxes cant understand

 

[JamesMcS]

Have you put them all in the code? i.e.

Case -1 'True

Checkbox1.value=-1
checkbox2.value=-1

and so on until you've got them all in