nepotist
12-18-2008, 07:37 AM
I have two comboboxes..
and here are the codes attached to it
first one
Private Sub cmbLand_AfterUpdate()
Dim land As String
Dim qrycategory As QueryDef
Dim db As Database
Dim strqry As String
Dim rstland As Recordset
Dim category As String
Dim qry As String
Dim Query3 As QueryDef
Dim count As Integer
On Error GoTo Line10:
land = cmbLand.Value
cmbcat.Value = ""
txtunits.Value = ""
txtrate.Value = ""
strqry = "SELECT LandUse.[LandUse Catogries], LandUse.[DTDLand Use] FROM LandUse WHERE (((LandUse.[LandUse Catogries]) = '" & land & "' ));"
Set db = CurrentDb()
Set rstland = db.OpenRecordset(strqry)
count = rstland.RecordCount
Debug.Print "count = " & count
Line1:
category = rstland.Fields(1) & "*"
qry = "SELECT DTDLU.[Land Use] FROM DTDLU WHERE DTDLU.[Land Use] Like (('" & category & "'));"
Set qrycategory = db.CreateQueryDef("Query3", qry)
cmbcat.RowSourceType = "Table/Query"
cmbcat.RowSource = "Query3"
Form_Form1.Refresh
Set db = Nothing
Set rstland = Nothing
DoCmd.Close acTable, "DTDLU"
' DoCmd.CloseDatabase acCmdCloseDatabase
Exit Sub
Line10:
If Err.Number = 3012 Then
DoCmd.DeleteObject acQuery, "Query3"
End If
GoTo Line1:
End Sub
the second cmbbox
Private Sub cmbcat_AfterUpdate()
Dim category As String
Dim db As Database
Dim strquery As String
Dim qry As QueryDef
Line1:
On Error GoTo Line10:
category = cmbcat.Value
' txtunits.Value = ""
'txtrate.Value = ""
strquery = "SELECT DTDLU.[Land Use], DTDLU.Units, DTDLU.[Net Cost Per Unit]FROM DTDLU WHERE (((DTDLU.[Land Use])='" & category & "'));"
Set db = CurrentDb()
Set qry = db.CreateQueryDef("Query2", strquery)
DoCmd.Close acTable, "DTDLU"
' Form_Form1.RecordsetType = "Dynaset"
Form_Form1.RecordSource = "Query2"
Form_Form1.Refresh
txtunits.ControlSource = "Units"
txtrate.ControlSource = "Net Cost Per Unit"
Set db = Nothing
Set qry = Nothing
Exit Sub
Line10:
If Err.Number = 3012 Then
DoCmd.DeleteObject acQuery, "Query2"
End If
If Err.Number = 3008 Then
DoCmd.Close acTable, "DTDLU"
End If
GoTo Line1:
End Sub
the first cmbbox works fine but the second cmbbox gives me a error 3008 that table DTDLU is opened exclusively by some other person or is already opened.
i have used close command to close but still it dosent work
I get the error at
Form_Form1.RecordSource = "Query2"
and when i turn this statement in to comment and i dont get the error.. but if I do that that wont let me update the two text boxes... can some one help me with this error .
THank you
and here are the codes attached to it
first one
Private Sub cmbLand_AfterUpdate()
Dim land As String
Dim qrycategory As QueryDef
Dim db As Database
Dim strqry As String
Dim rstland As Recordset
Dim category As String
Dim qry As String
Dim Query3 As QueryDef
Dim count As Integer
On Error GoTo Line10:
land = cmbLand.Value
cmbcat.Value = ""
txtunits.Value = ""
txtrate.Value = ""
strqry = "SELECT LandUse.[LandUse Catogries], LandUse.[DTDLand Use] FROM LandUse WHERE (((LandUse.[LandUse Catogries]) = '" & land & "' ));"
Set db = CurrentDb()
Set rstland = db.OpenRecordset(strqry)
count = rstland.RecordCount
Debug.Print "count = " & count
Line1:
category = rstland.Fields(1) & "*"
qry = "SELECT DTDLU.[Land Use] FROM DTDLU WHERE DTDLU.[Land Use] Like (('" & category & "'));"
Set qrycategory = db.CreateQueryDef("Query3", qry)
cmbcat.RowSourceType = "Table/Query"
cmbcat.RowSource = "Query3"
Form_Form1.Refresh
Set db = Nothing
Set rstland = Nothing
DoCmd.Close acTable, "DTDLU"
' DoCmd.CloseDatabase acCmdCloseDatabase
Exit Sub
Line10:
If Err.Number = 3012 Then
DoCmd.DeleteObject acQuery, "Query3"
End If
GoTo Line1:
End Sub
the second cmbbox
Private Sub cmbcat_AfterUpdate()
Dim category As String
Dim db As Database
Dim strquery As String
Dim qry As QueryDef
Line1:
On Error GoTo Line10:
category = cmbcat.Value
' txtunits.Value = ""
'txtrate.Value = ""
strquery = "SELECT DTDLU.[Land Use], DTDLU.Units, DTDLU.[Net Cost Per Unit]FROM DTDLU WHERE (((DTDLU.[Land Use])='" & category & "'));"
Set db = CurrentDb()
Set qry = db.CreateQueryDef("Query2", strquery)
DoCmd.Close acTable, "DTDLU"
' Form_Form1.RecordsetType = "Dynaset"
Form_Form1.RecordSource = "Query2"
Form_Form1.Refresh
txtunits.ControlSource = "Units"
txtrate.ControlSource = "Net Cost Per Unit"
Set db = Nothing
Set qry = Nothing
Exit Sub
Line10:
If Err.Number = 3012 Then
DoCmd.DeleteObject acQuery, "Query2"
End If
If Err.Number = 3008 Then
DoCmd.Close acTable, "DTDLU"
End If
GoTo Line1:
End Sub
the first cmbbox works fine but the second cmbbox gives me a error 3008 that table DTDLU is opened exclusively by some other person or is already opened.
i have used close command to close but still it dosent work
I get the error at
Form_Form1.RecordSource = "Query2"
and when i turn this statement in to comment and i dont get the error.. but if I do that that wont let me update the two text boxes... can some one help me with this error .
THank you