sir
i want if user click in combo box corresponding value should come
from pquery, mquery from access database.

but i am not getting the result i have put some code in combo_click
but when i click in text combo box to display the last value of product_id
then i am getting error
run time error 3021 kindly help me

Private Sub Combo1_Click()
Dim nm As String
Set rscombo = New Recordset
Select Case Combo1.Text
Case "Production Consumable"
rscombo.Open "select * from Pquery", con, adOpenDynamic, adLockOptimistic
MsgBox (rscombo.Fields(0))
List1.AddItem rscombo!Fields(item_code)
Case "Maintenance Consumable"
rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic
Case Else
rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic
End Select
End Sub

Filter your query by comparing the field related to the combobox text,
may be something like this,

rscombo.Open "select * from Pquery where item_name ='" & Combo1.Text & "'", con, adOpenDynamic, adLockOptimistic
Private Sub Combo1_Click()

    Dim nm As String

    Set rscombo = New Recordset

    Select Case Combo1.Text

        Case "Production Consumable"
            rscombo.Open "select * from Pquery where item_name ='" & Combo1.Text & "'", con, adOpenDynamic, adLockOptimistic
            If rscombo.BOF = False Then
                MsgBox (rscombo.Fields(0))
                List1.AddItem rscombo!Fields(item_code)
            End If

        Case "Maintenance Consumable"
            rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic

        Case Else
            rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic

    End Select
End Sub
If rscombo.BOF = False Then

This statement is to check if any record bearing that item_name has been retreived/not. if BOF property is True then there are no records satisfying the criteria.

Also change this statement

List1.AddItem rscombo.Fields("item_code")

or

List1.AddItem rscombo!item_code

sir
kindly find the imp attachment

i am waiting for your instant response .

1. ENSURE THAT THERE WILL BE NO SPACES IN ANY OF THE "ITEM_CODE" VALUES.
2. YOU MUST BE SURE THAT THE FIRST LETTER IS DEFINITELY AN ALPHABET "P" AND THE REMAINING PART OF THE FIELD LOOKS LIKE A NUMBER WITHOUT ANY OTHER CHARACTERS.
3. CONSIDER THE SIZE OF THE FIELD FIRSTLY CHECK THE SIZE OF THE FIELD "ITEM_CODE"

U CAN USE THIS FOLLOWING QUERY

Private Sub Combo1_Click()

    Dim nm As String
    Set rscombo = New Recordset

    Select Case Combo1.Text
        Case "Production Consumable"
            'In this query replace 10 with a value equal to (size of your ITEM_CODE field - 1)
            rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY", con, adOpenDynamic, adLockOptimistic
            MsgBox (rscombo.Fields(0))
            'Here rscombo!MAXID is the last number. To show the next required number
            'just add 1 to it and display. follow the same logic for others also
            List1.AddItem rscombo!MAXID + 1

        Case "Maintenance Consumable"
            rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic

        Case Else
            rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic
    End Select
End Sub

IF YOUR ITEM_CODE FIELD HAS MULTIPLE TYPES OF VALUES LIKE "P199", "S23", "S76", "U876" U HAVE TO MODIFY THE QUERY AS BELOW

rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'P' ", con, adOpenDynamic, adLockOptimistic
'OR
    rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'U' ", con, adOpenDynamic, adLockOptimistic

1. ENSURE THAT THERE WILL BE NO SPACES IN ANY OF THE "ITEM_CODE" VALUES.
2. YOU MUST BE SURE THAT THE FIRST LETTER IS DEFINITELY AN ALPHABET "P" AND THE REMAINING PART OF THE FIELD LOOKS LIKE A NUMBER WITHOUT ANY OTHER CHARACTERS.
3. CONSIDER THE SIZE OF THE FIELD FIRSTLY CHECK THE SIZE OF THE FIELD "ITEM_CODE"

U CAN USE THIS FOLLOWING QUERY

Private Sub Combo1_Click()

    Dim nm As String
    Set rscombo = New Recordset

    Select Case Combo1.Text
        Case "Production Consumable"
            'In this query replace 10 with a value equal to (size of your ITEM_CODE field - 1)
            rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY", con, adOpenDynamic, adLockOptimistic
            MsgBox (rscombo.Fields(0))
            'Here rscombo!MAXID is the last number. To show the next required number
            'just add 1 to it and display. follow the same logic for others also
            List1.AddItem rscombo!MAXID + 1

        Case "Maintenance Consumable"
            rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic

        Case Else
            rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic
    End Select
End Sub

IF YOUR ITEM_CODE FIELD HAS MULTIPLE TYPES OF VALUES LIKE "P199", "S23", "S76", "U876" U HAVE TO MODIFY THE QUERY AS BELOW

rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'P' ", con, adOpenDynamic, adLockOptimistic
'OR
    rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'U' ", con, adOpenDynamic, adLockOptimistic

Kindly find the attachment.

Case Else
    rscombo.Open "select item_code from production  WHERE item_code is not NULL  and  LEFT(Production.ITEM_CODE,1) = 'P' order by item_code ", con, adOpenDynamic, adLockOptimistic
'U need not write rscombo.MoveFirst b'cos 
'the recordset when opened will be positioned at the First Record only
'    rscombo.MoveFirst
    Do Until rscombo.EOF
        List1.AddItem rscombo.Fields("item_code")
        rscombo.MoveNext
    Loop
    Exit Sub
End Select

If any leading spaces may be in the item_code field then use the trim function.

Case Else
    rscombo.Open "select item_code from production  WHERE item_code is not NULL  and  LEFT(Trim(Production.ITEM_CODE),1) = 'P' order by item_code ", con, adOpenDynamic, adLockOptimistic
'U need not write rscombo.MoveFirst b'cos 
'the recordset when opened will be positioned at the First Record only
'    rscombo.MoveFirst
    Do Until rscombo.EOF
        List1.AddItem rscombo.Fields("item_code")
        rscombo.MoveNext
    Loop
    Exit Sub
End Select

since u have both upper case and lower case alphabets in the item_code use UCase in the query for comparison.

Case Else
    rscombo.Open "select item_code from production  WHERE item_code is not NULL  and  LEFT(UCase(Trim(Production.ITEM_CODE)),1) = 'P' order by item_code ", con, adOpenDynamic, adLockOptimistic
'U need not write rscombo.MoveFirst b'cos 
'the recordset when opened will be positioned at the First Record only
'    rscombo.MoveFirst
    Do Until rscombo.EOF
        List1.AddItem rscombo.Fields("item_code")
        rscombo.MoveNext
    Loop
    Exit Sub
End Select

UCase() function converts any alphabet to its Upper Case. If the database is SQL Server use Upper()

i am using login form when i make emp_id datatype text it is working fine.
rs.Open "select * from EMPID where EMP_ID='" & Text1.Text & "'", con, adOpenDynamic, adLockOptimistic
but when using emp_id integer it is not working
.Open "select * from employee_record where employee_id=" & Val(Text1.Text)

kindly help me
since employee_id as per field it should be numeric that is why i did integer datatype but it is not working properly

I want if user select row from msflexgrid1 control
it should go in another msflexgrid2 respectively .
Kindly help me
i have written some code in form_load is like that.
Private Sub Form_Load()
MSFlexGrid1.FixedRows = 1
MSFlexGrid1.FillStyle = flexFillRepeat
MSFlexGrid1.ColWidth(0) = 1000
MSFlexGrid1.ColWidth(1) = 3500
MSFlexGrid1.ColAlignment(0) = 4
MSFlexGrid1.ColAlignment(1) = 4
MSFlexGrid1.ColAlignment(2) = 4
MSFlexGrid1.Row = 1
MSFlexGrid1.Col = 0
MSFlexGrid1.RowSel = 5
MSFlexGrid1.Cols = 10
MSFlexGrid1.Rows = 10
On Error Resume Next
MSFlexGrid1.CellFontBold = True
MSFlexGrid1.CellAlignment = 4
MSFlexGrid1.Font = "Arial"
MSFlexGrid1.TextMatrix(0, 0) = "Product_id"
MSFlexGrid1.TextMatrix(0, 1) = "Product_name"
MSFlexGrid1.TextMatrix(0, 2) = "Unit"
MSFlexGrid1.TextMatrix(0, 3) = "Quantity"
Dim R, C
Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Open "select distinct item_code,Productname,unit from partdetail ", con, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Do Until rs.EOF
R = R + 1
For C = 0 To 4 Step 1
MSFlexGrid1.Rows = rs.RecordCount + 1
MSFlexGrid1.TextMatrix(R, C) = rs.Fields(C).Value
Next
rs.MoveNext
Loop
End If
End Sub

Kindly find attachment.doc also

I am waiting for your instant reponse sir

In a msflexgrid control, i want to give the option to the user to select a single row or a group of rows if user click the msflexgrid1
if user click product_id from fixed column automatic the selected
item should go in another flexgridcontrol.
i have written a code in form_load event
Private Sub Form_Load()
Set con = New Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
Combo1.AddItem "ADMIN & IT"
Combo1.AddItem "SALES"
Combo1.AddItem "ENGINEERING"
Combo1.AddItem "MAINTENANCE"
Combo1.AddItem "MANUFACTERING"
Combo1.AddItem "FINANCE"
Combo1.AddItem "QUALITY CONTROL"
Text13.Locked = Not editing
MSFlexGrid1.FixedRows = 1
MSFlexGrid1.FillStyle = flexFillRepeat
MSFlexGrid1.ColWidth(0) = 1000
MSFlexGrid1.ColWidth(1) = 3500
MSFlexGrid1.ColAlignment(0) = 4
MSFlexGrid1.ColAlignment(1) = 4
MSFlexGrid1.ColAlignment(2) = 4
MSFlexGrid1.Row = 1
MSFlexGrid1.Col = 0
MSFlexGrid1.RowSel = 5
MSFlexGrid1.Cols = 10 'Set dimensions of flexgrid and set selection behaviour
MSFlexGrid1.Rows = 10 'displaying grid
On Error Resume Next
MSFlexGrid1.CellFontBold = True
MSFlexGrid1.CellAlignment = 4
MSFlexGrid1.Font = "Arial"
MSFlexGrid1.TextMatrix(0, 0) = "Product_id"
MSFlexGrid1.TextMatrix(0, 1) = "Product_name"
MSFlexGrid1.TextMatrix(0, 2) = "Unit"
MSFlexGrid1.TextMatrix(0, 3) = "Quantity"
Dim R, C
Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Open "select distinct item_code,Productname,unit from partdetail ", con, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Do Until rs.EOF
R = R + 1
For C = 0 To 4 Step 1
MSFlexGrid1.Rows = rs.RecordCount + 1
MSFlexGrid1.TextMatrix(R, C) = rs.Fields(C).Value
Next
rs.MoveNext
Loop
End If
End Sub

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.