Member Avatar for firoz.raj

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

  • 2 Contributors
  • 12 Replies
  • 128 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by firoz.raj

Recommended Answers

All 12 Replies

Member Avatar for aktharshaik

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
Member Avatar for aktharshaik 
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
Member Avatar for aktharshaik 
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.

Member Avatar for aktharshaik

Also change this statement

List1.AddItem rscombo.Fields("item_code")

or

List1.AddItem rscombo!item_code
Member Avatar for firoz.raj

sir
kindly find the imp attachment

i am waiting for your instant response .

imp.doc (74.5 KB)
Member Avatar for aktharshaik

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
Member Avatar for firoz.raj

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.

test.doc (26 KB)
Member Avatar for aktharshaik 
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
Member Avatar for aktharshaik

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()

Member Avatar for firoz.raj

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

Member Avatar for firoz.raj

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

Member Avatar for firoz.raj

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

imp.doc (63.5 KB)
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.