HELP! Combobox Requery/Selection Problem in Access VB

[techwayservices]

Ok I have been programming in VB access for about 2 weeks and need help. I just found out how to query the database and load information into the combobox which is great but it still has bugs. When the user clicks on the equipment or the manufacture combobox I need it to requery(get rid of existing values which it is doing) and populate the model combobox(will not and giving me the error that it has no current recordset). My second issue is when I try to select the value it does not select. I created a counter ctr so it wont keep populating after multiple clicks. Here is my code thus far:


Dim ctr As Integer
Public Sub Event_Counter()

ctr = ctr + 1
End Sub


Private Sub btn_add_inv_entry_Click()


On Error GoTo Err_btn_add_inv_entry_Click

Dim stDocName As String


Exit_btn_add_inv_entry_Click:
Exit Sub

Err_btn_add_inv_entry_Click:
MsgBox Err.Description
Resume Exit_btn_add_inv_entry_Click

End Sub


Private Sub cbo_equip_type_Click()

cbo_Model_No.RowSource = vbNullString
cbo_Model_No.Requery
ctr = 0
End Sub

Private Sub cbo_MFG_Click()
ctr = 0
cbo_Model_No.RowSource = vbNullString
cbo_Model_No.Requery
ctr = 0


End Sub

Private Sub cbo_Model_No_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
Dim MFG As Variant
Dim EquipType As Variant

MFG = cbo_MFG.Value
EquipType = cbo_equip_type.Value
cbo_Model_No.RowSourceType = "Value List"

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\Fileserver\Data\Inventorydb\Techway.mdb;" & _
"Persist Security Info=False"

Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
sSQL = "Select ID, Model_No From dbo_MFG_Model_No Where MFG_Name Like '" & MFG & "' And Equip_Type Like '" & EquipType & "'"
adoRS.Open sSQL, ADOCn



If ctr = 0 Then
Do Until adoRS.EOF
'It is Crapping out saying no recordset and requery I suspect is the culprit
cbo_Model_No.AddItem adoRS.Fields.Item("Model_No").Value
adoRS.MoveNext
Loop
End If


adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing
Event_Counter
End Sub

[Hack]

Why are you using the MouseDown event as opposed to the Click event?

You LIKE statements are missing the necessary wildcard character. With Access, the wildcard character is the *

[techwayservices]

I am not using a access database but a sql database on the backend. I will try mouse click and see what that does.

Thank you

_Stephen

[techwayservices]

Ok. Tried that. Now it doesnt work at all. It liked the mouse down event better for some reason. Any ideas?

_Stephen

[c03cg]

Dim ctr As Integer
Public Sub Event_Counter()

ctr = ctr + 1
End Sub


Private Sub btn_add_inv_entry_Click()


On Error GoTo Err_btn_add_inv_entry_Click

Dim stDocName As String


Exit_btn_add_inv_entry_Click:
Exit Sub

Err_btn_add_inv_entry_Click:
MsgBox Err.Description
Resume Exit_btn_add_inv_entry_Click

End Sub


Private Sub cbo_equip_type_Click()

cbo_Model_No.RowSource = vbNullString
cbo_Model_No.Requery
ctr = 0
End Sub

Private Sub cbo_MFG_Click()
ctr = 0
cbo_Model_No.RowSource = vbNullString
cbo_Model_No.Requery
ctr = 0


End Sub

Private Sub cbo_Model_No_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
Dim MFG As Variant
Dim EquipType As Variant

MFG = cbo_MFG.Value
EquipType = cbo_equip_type.Value
cbo_Model_No.RowSourceType = "Value List"

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\Fileserver\Data\Inventorydb\Techway.mdb;" & _
"Persist Security Info=False"

Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
sSQL = "Select ID, Model_No From dbo_MFG_Model_No Where MFG_Name Like '" & MFG & "' And Equip_Type Like '" & EquipType & "'"
adoRS.Open sSQL, ADOCn



If ctr = 0 Then
Do Until adoRS.EOF
'It is Crapping out saying no recordset and requery I suspect is the culprit
cbo_Model_No.AddItem adoRS.Fields.Item("Model_No").Value
adoRS.MoveNext
Loop
End If


adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing
Event_Counter
End Sub


just because its 2am.. and i need to save my poor little eyes

[c03cg]

Why not just use the values in the SQL statement as they are?:

sSQL = "SELECT ID, Model_No FROM dbo_MFG_Model_No WHERE MFG_Name LIKE [cbo_MFG.Value] AND Equip_Type LIKE [cbo_equip_type.Value]

[techwayservices]

Ya I could have done that. But that still didnt solve my problem just made the code look prettier. The solution was a AfterUpdate event on the two comboboxs that will requery. I ended up getting rid of the code and just plugging in the information into a query on the row source on the comboboxs design mode and it worked alot better. Microsoft Office has a example on bound comboboxes that helped me alot.

_Stephen

[techwayservices]

Great Link to learn how to bind a combobox to another using Sql statements.



http://office.microsoft.com/en-us/assistance/HA011730581033.aspx


_Stephen