Solved: RunTime error 3008

[nepotist]

I have two comboboxes..
and here are the codes attached to it
first one
[VBA]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
[/VBA]

the second cmbbox
[VBA]
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[/VBA]

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
[VBA]Form_Form1.RecordSource = "Query2"[/VBA]
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

[CreganTur]

The error occurs because you have already opened table DTDLU as a recordset. Therefore Access knows that the table is open and in use, so JET disallows a second opening of the table, which is what you're trying to do when you change your form's record source.

You could push values to your form's objects manually by pulling the values form your recordset's field indexes.

[nepotist]

Hey randy.. i tried doing that for some reason it is able to pull values for a couple of records and not for other.. I dont know why.. ??

[nepotist]

My bad .. it was coz the data was edited back in the table ..

I have a couple of small questions..

when I load the form I want the combobox to show the first record.. .. like if the query is in sorted in alphabetical order it show A as a default..

when i initially designed the form it showed me the first one as a default one but at times it dosent show ... how can i make it to show the first record all the time.

I know that I can set the default value property of the cmbbox.. but this my change as the user adds data ..

[CreganTur]

when i initially designed the form it showed me the first one as a default one but at times it dosent show ...

Do you mean that it doesn't show any records, or that it is showing a record other than what you expect the first record to be?

You may have to filter your recordset to get it to show in alphabetical order.

[nepotist]

Hey randy.. I am done with a part of my application and have attached with this post please find..

Before you open the form and play around with it , please open table DTDLU and i want to to look at the values for as once you playaround with cmbboxes the values change. I have made sure that the control source proerty is left blank but still it changes the values.

when you open the form you see inital values in two text boxes those actually are values for the Dairy landuse which falls in the multifamily category (you can see this in table landuse and the the text box values are from table dtdlu for multifamily..
In my above post what I meant was this as I can see the values of in textboxes but not in the combo boxes , how can i see the inital value loaded.. instead of blank.

And I also wheni play around with the application, the size of the application increases.. i made sure that all the recordsets and database are closed and set to nothing at the end of the module.

Can some one please look in to this application and give me feedback of how I can improve it and it you guys think I did some thing wrong.

Please it is a small application..
I appreciate you time and replies.. it will help me in future
Thank you

[CreganTur]

Before you open the form and play around with it , please open table DTDLU and i want to to look at the values for as once you playaround with cmbboxes the values change.

I played with the comboboxes and it did not change the values in this table.

In my above post what I meant was this as I can see the values of in textboxes but not in the combo boxes , how can i see the inital value loaded.. instead of blank.

Set a value for the Default property of the comboboxes.

And I also wheni play around with the application, the size of the application increases

If you're talking about the file size of the actual .mdb, then this is normal. As you add records to it the size of the file will increase. Even if you add a bunch of records and then delete them the same way the database can become bloated. That's why there is a Compact and Repair tool. You probably don't need to use it very often, unless your database handles large volumes of data.

Can some one please look in to this application and give me feedback of how I can improve it and it you guys think I did some thing wrong.

I would suggest using the Form's Change event to calculate your Impact fee. When I change the combobox values the inpact fee would not recaculate unless I changed the size. Using the change event would cause a recaulculation whenever anything on the form is changed. For such a small calculator application the overhead wouldn't be an issue.

[nepotist]

Hey randy thanks for the suggestion I did incorporate all your suggestions..

I played with the comboboxes and it did not change the values in this table.

well it actually does.. if i remember exactly the value for the multifamily in the table dtdlu was about $1245.
Try playing with just the two cmbo boxes and close the form adn reopen it.. it changes the value .. to some outer thing..

Once again Thank you

[nepotist]

Can some one please answer me the following questions, Please.

1) In the above attachment , if you open the form , the default values in for the units and rate are the "Dairy and Multifamily landuse(I mean the first cmb box will be dairy and the second will be multifamily, based on the table dtdlu) Why am I seeing those values there.
I could fix it by making the default values of the above cmbboxes to dairy and multifamily . that works
2) as mentioned in (1) the default values being dairy and multifamily. Now when you change the cmb boxes of any thing different and close the form( before you close the form make a note of the units and rates) reopen the form back again now the default values are same as wht you had seen before you close the form( that is the value in the table for mulltifamily are changes to these values) I dont know why is this happening and it happens only for the Multifamily landuse.

Can some one please help me figure out what I am missing??

[nepotist]

Never mind got it