The form works as long as I dont add the StateName to it except for it doesnt put the "StateName" in the Selected WorkArea list box. It just puts in the "State"
If I add the StateName to this, then it gives me this error.
Can anyone tell me how to fix this please. The destination is to have a list box with available states and StateNames in it, then put selected ones into SelecteWorkArea listbox,
and save to Table and then on load put those for the CompanyID that were previously selected back in that Selected WorkArea list box. If i add to it, then save, if delete, then remove
and save.
The on load code seems to be the issue and I do not know how to fix that code to add "StateName" to it as i am lost after the C. and R. stuff?
I put the code in RED that is triggering the Debug Event....
Thanks
See Code
Code:
Option Compare Database
Option Explicit
Private Sub cboCompanyID_AfterUpdate()
If Nz(Me.cboCompanyID, 0) > 0 Then
DoEvents
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tblStateSelect (StateID, State, StateName) Select C.StateID, R.State From tblCompanyWorkArea C Inner Join tblState R ON C.StateID = R.StateID Where CompanyID = " & Nz(Me.cboCompanyID, 0)
DoCmd.SetWarnings True
DoEvents
Me.cboSelectWorkArea.Requery
End If
End Sub
Private Sub cmdAddWorkArea_Click()
On Error Resume Next
Dim ctl As Control
Dim Itm As Variant
DoCmd.SetWarnings False
Set ctl = Me.cboAvailableWorkArea
For Each Itm In ctl.ItemsSelected
DoCmd.RunSQL "Insert Into tblStateSelect (StateID, State, StateName) Select StateID, State, StateName From tblState Where StateID = " & ctl.ItemData(Itm)
Next Itm
DoCmd.SetWarnings True
Me.cboSelectWorkArea.Requery
End Sub
Private Sub cmdAddWorkAreaAll_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "WorkAreaAppQry"
DoCmd.SetWarnings True
Me.cboSelectWorkArea.Requery
End Sub
Private Sub cmdRemoveWorkArea_Click()
On Error Resume Next
Dim ctl As Control
Dim Itm As Variant
DoCmd.SetWarnings False
Set ctl = Me.cboSelectWorkArea
For Each Itm In ctl.ItemsSelected
DoCmd.RunSQL "Delete From tblStateSelect Where StateID = " & ctl.ItemData(Itm)
Next Itm
DoCmd.SetWarnings True
Me.cboSelectWorkArea.Requery
End Sub
Public Sub CmdRemoveWorkAreaAll_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From tblStateSelect"
DoCmd.SetWarnings True
Me.cboSelectWorkArea.Requery
End Sub
Private Sub cmdSave_Click()
CurrentDb.Execute "Delete * From tblCompanyWorkArea Where CompanyID = " & Nz(Me.cboCompanyID, 0)
CurrentDb.Execute "Insert INTO tblCompanyWorkArea (CompanyID, StateID, State, StateName) SELECT " & Nz(Me.cboCompanyID, 0) & ",StateID , State , StateName From tblStateSelect"
DoEvents
End Sub
Private Sub Form_Current()
Me.Form.Refresh
End Sub
Private Sub Form_Load()
doClear
If Nz(Me.cboCompanyID, 0) > 0 Then
DoEvents
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tblStateSelect (StateID, State, StateName) Select C.StateID, R.State From tblCompanyWorkArea C Inner Join tblState R ON C.StateID = R.StateID Where CompanyID = " & Nz(Me.cboCompanyID, 0)
DoCmd.SetWarnings True
DoEvents
Me.cboSelectWorkArea.Requery
Me.cboAvailableWorkArea.Requery
End If
End Sub
Public Sub doClear()
Me.CmdRemoveWorkAreaAll_Click
End Sub