combo box selection

[bearfish]

Hi, I would like my combo box to response when a selection is pick. For my code below:
If CPT is selected from the combo, I would like to enquire from the Computer table and get the last record(its auto increment) and save it to a variable and increment it by 1, then display it to the screen. But i get Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal. Can anyone explain to me why does this error occur corresponding to my code? Thank you.

Private Sub cboEqpType_Click()
Dim sCptCount As Integer
sCptCount = 0
If cboEqpType.Text = "CPT" Then
moCommand.CommandType = adCmdText
moCommand.ActiveConnection = moConnection
moCommand.CommandText = _
"SELECT MAX(CptID) FROM Computer;"
'Run the query
Set rsMain = moCommand.Execute

sCptCount = rsMain!CptID
sCptCount = sCptCount + 1
txtEDPControlNo = sCptCount
txtEDPControlNo.Enabled = False
cboEqpType.Enabled = False
End If
End Sub

 

[mattKnight]

alter

moCommand.CommandText = _
            "SELECT MAX(CptID) FROM Computer;"

to

moCommand.CommandText = _
            "SELECT MAX(CptID) as MyMax FROM Computer;"
[code]

and
[code]
sCptCount = rsMain!CptID

to

sCptCount = rsMain!myMax ' or to rsMain.Fields.item(0).value

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[bearfish]

thanks alot. i got it to work. Now i have another question. I've create a ListBox which display all the records in DB when queried. I would my ListBox to response when double click on the item(only 1 item can be double click)it will get the EdpControlNo & EqpCategoryType and display at the form based on the InventoryID which is the primary key for each record in Inventory table. Below are my code, and when i double click on the item on listbox, i got mismatch data type.Whats wrong with my codes in lstRecord_DblClick() procedures?

'To display all the records from DB
Private Sub mnuFindList_Click()
Dim sEqpCatID As String
Dim sEdpControlNo As String
Dim sEqpSerialNo As String

moCommand.CommandType = adCmdText
moCommand.ActiveConnection = moConnection
moCommand.CommandText = _
"SELECT InventoryID, EqpCategoryType, EdpControlNo, EqpSerialNo FROM INVENTORY;"

'Run the query
Set rsMain = moCommand.Execute

If (rsMain.EOF And rsMain.BOF) Then
'Display error msg
Else
lstRecord.Clear
'Loop Through entire record set
Do While (Not rsMain.EOF)
'Assign each field value in turn to a local variable
sInventoryID = rsMain!InventoryID
sEqpCatID = rsMain!EqpCategoryType
sEdpControlNo = rsMain!EdpControlNo
sEqpSerialNo = rsMain!EqpSerialNo

lstRecord.AddItem sInventoryID & ", " & sEqpCatID & ", " & sEdpControlNo & ", " & sEqpSerialNo
rsMain.MoveNext
Loop 'End of do while not EOF loop
End If 'End if EOF and BOF
End Sub

Private Sub lstRecord_DblClick()
'Dim sSQL4 As String

moCommand.CommandType = adCmdText
moCommand.ActiveConnection = moConnection
moCommand.CommandText = _
"SELECT EqpCategoryType, EdpControlNo FROM Inventory WHERE InventoryID = '" & sInventoryID & "';"

Set rsMain = moCommand.Execute
Me.cboEqpCategoryType = rsMain!EqpCategoryType
Me.txtEdpControlNo = rsMain!EdpControlNo

End Sub

 

[CCLINT]

>get the last record(its auto increment) and save it to a variable and increment it by 1.

Not a good idea to use this value as the new AutoIncrement number in a multi-user environment.

Best is to let the DBMS handle it...

 

[mattKnight]

Try fully specifying the property that you are setting.

I can't remember which property is the default for the combobox, so it is possible that is where the problem lies!

Take Care

Matt
If at first you don't succeed, skydiving is not for you.