So you have a field in a table named ShortID and a function on this form named shortID()? That's a formula for confusing Access and it does not take too much to confuse it, believe me. Is there a chance of renaming one of them to maybe shortID2 or something.
On a separate subject, do you have Name Auto-Correct turned off? Tools>Options>General tab.
Private Sub chkNoSupportHrs_Click()
On Error GoTo Err_ChkBox_Click
Dim MySQL As String
[b]Dim db As DAO.Database
Set db = CurrentDB()[/b]
If Me.chkNoSupportHrs Then
'-- CheckBox going to "Checked" state
MySQL = "Insert Into tblNoSupportHrs(sID,WeekEnding) " & _
"Values(" & Me.txtResource & ", """ & Me.Text18 & """)"
Else
'-- CheckBox going to "UnChecked" state
MySQL = "DELETE * FROM tblNoSupportHrs WHERE [sID] = " & Me.txtResource
End If
[b]'MsgBox MySql
db.Execute MySQL, dbFailOnError[/b]
Exit_ChkBox_Click:
[b]Set db = Nothing[/b]
Exit Sub
Err_ChkBox_Click:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_ChkBox_Click
End Sub
I'm glad I could help. I don't know if you are using any ADO but if not, you might try unchecking the reference to ActiveX and recompile. If there are no errors then leave it unchecked. Post back if you need further assistance.
I'm glad I could help. I don't know if you are using any ADO but if not, you might try unchecking the reference to ActiveX and recompile. If there are no errors then leave it unchecked. Post back if you need further assistance.
I notice that when i have a previous week selected, and i go into this form. The data from the previous week is populated in the form already (the form is bound to a table).
But if the person checked the chkNoSupportHrs checkbox lastweek and they go in a week after. The checkbox is not checked (it is not saved).
Do you think it will be wise for me to have bound this table also (that's if you can bound 2 tables).
Or
Attach VBA to the onload event of this form, so that it checks the tblNoSupportHrs table and if there's a match for that person for that week, then it keeps box checked.
I'm sorry MsLady, but I don't really understand your forms and tables. I've just been troubleshooting a specific problem so far. Since you have no True/False field in the tblNoSupportHrs table you are left with adding some VBA to the OnLoad event to set up your form. At least as far as I can tell knowing what I know so far. I hope that helps.
I'm sorry MsLady, but I don't really understand your forms and tables. I've just been troubleshooting a specific problem so far. Since you have no True/False field in the tblNoSupportHrs table you are left with adding some VBA to the OnLoad event to set up your form. At least as far as I can tell knowing what I know so far. I hope that helps.
*ahem
Rural Guy, i have come again..
LOL.. i refuse to go away
As you may know already, I am attaching this code to the onload event of my form bound to a table.
And i want it to check a chkbox on this form if some data on this form (Me.Text18, Me.txtResource) exists in "another" table (tblNoSupportHrs)
Here is what i have in my onload event:
Code:
On Error GoTo Err_ChkBox_Click
Dim db As DAO.Database
Set db = CurrentDb()
Dim MySQL As String, DQ As String
DQ = """"
MySQL = "SELECT [WeekEnding] from tblNoSupportHrs WHERE [sID] = " & DQ & Me.txtResource & DQ & _
" And [WeekEnding] = " & DQ & Me.Text18 & DQ & ";"
If Me.Text18 = MySQL Then
Me.chkNoSupportHrs = True
Else
End If
db.Execute MySQL, dbFailOnError
Exit_ChkBox_Click:
Set db = Nothing
Exit Sub
Err_ChkBox_Click:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_ChkBox_Click
End Sub
However, i get this error: When i load my form. Error 3065, "cannot execute a select query"
I am aware that the Execute method can be used for action queries only (UPDATE, INSERT, DELETE, ALTER, ...) and it won't work since i am trying to "SELECT" here.
Someone suggested i will need to play with my Recordset or the DLookUp function.
I don't know how to do this.
Any thoughts, suggestions, ideas, curses? anything
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim db As DAO.Database
Dim MyRs As DAO.Recordset
Dim MySQL As String
Set db = CurrentDb()
MySQL = "SELECT * from tblNoSupportHrs WHERE [sID] = " & Me.txtResource & _
" And [WeekEnding] = '" & Me.Text18 & "';"
Set MyRs = db.OpenRecordset(MySQL, dbOpenDynaset)
If MyRs.RecordCount > 0 Then
Me.chkNoSupportHrs = True
Else
Me.chkNoSupportHrs = False
End If
Exit_Form_Load:
On Error Resume Next
MyRs.Close
Set MyRs = Nothing
Set db = Nothing
Exit Sub
Err_Form_Load:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_Form_Load
End Sub
