help

Reply
1 2

•
•
Join Date: Dec 2008
Posts: 356
Reputation: firoz.raj is an unknown quantity at this point 
Solved Threads: 1
firoz.raj firoz.raj is offline Offline
Posting Whiz

help

 
0
  #1
Dec 6th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 306
Reputation: aktharshaik is an unknown quantity at this point 
Solved Threads: 37
aktharshaik's Avatar
aktharshaik aktharshaik is offline Offline
Posting Whiz

Re: help

 
0
  #2
Dec 6th, 2008
Filter your query by comparing the field related to the combobox text,
may be something like this,
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. rscombo.Open "select * from Pquery where item_name ='" & Combo1.Text & "'", con, adOpenDynamic, adLockOptimistic
Regards
Shaik Akthar
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 306
Reputation: aktharshaik is an unknown quantity at this point 
Solved Threads: 37
aktharshaik's Avatar
aktharshaik aktharshaik is offline Offline
Posting Whiz

Re: help

 
0
  #3
Dec 6th, 2008
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. Private Sub Combo1_Click()
  2.  
  3.     Dim nm As String
  4.  
  5.     Set rscombo = New Recordset
  6.  
  7.     Select Case Combo1.Text
  8.  
  9.         Case "Production Consumable"
  10.             rscombo.Open "select * from Pquery where item_name ='" & Combo1.Text & "'", con, adOpenDynamic, adLockOptimistic
  11.             If rscombo.BOF = False Then
  12.                 MsgBox (rscombo.Fields(0))
  13.                 List1.AddItem rscombo!Fields(item_code)
  14.             End If
  15.  
  16.         Case "Maintenance Consumable"
  17.             rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic
  18.  
  19.         Case Else
  20.             rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic
  21.  
  22.     End Select
  23. End Sub
Last edited by aktharshaik; Dec 6th, 2008 at 7:53 am.
Regards
Shaik Akthar
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 306
Reputation: aktharshaik is an unknown quantity at this point 
Solved Threads: 37
aktharshaik's Avatar
aktharshaik aktharshaik is offline Offline
Posting Whiz

Re: help

 
0
  #4
Dec 6th, 2008
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1.  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.
Regards
Shaik Akthar
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 306
Reputation: aktharshaik is an unknown quantity at this point 
Solved Threads: 37
aktharshaik's Avatar
aktharshaik aktharshaik is offline Offline
Posting Whiz

Re: help

 
0
  #5
Dec 6th, 2008
Also change this statement

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. List1.AddItem rscombo.Fields("item_code")

or
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. List1.AddItem rscombo!item_code
Last edited by aktharshaik; Dec 6th, 2008 at 8:00 am.
Regards
Shaik Akthar
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 356
Reputation: firoz.raj is an unknown quantity at this point 
Solved Threads: 1
firoz.raj firoz.raj is offline Offline
Posting Whiz

Re: help

 
0
  #6
Dec 6th, 2008
sir
kindly find the imp attachment

i am waiting for your instant response .
Attached Files
File Type: doc imp.doc (74.5 KB, 4 views)
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 306
Reputation: aktharshaik is an unknown quantity at this point 
Solved Threads: 37
aktharshaik's Avatar
aktharshaik aktharshaik is offline Offline
Posting Whiz

Re: help

 
0
  #7
Dec 8th, 2008
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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1.  
  2. Private Sub Combo1_Click()
  3.  
  4.     Dim nm As String
  5.     Set rscombo = New Recordset
  6.  
  7.     Select Case Combo1.Text
  8.         Case "Production Consumable"
  9.             'In this query replace 10 with a value equal to (size of your ITEM_CODE field - 1)
  10.             rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY", con, adOpenDynamic, adLockOptimistic
  11.             MsgBox (rscombo.Fields(0))
  12.             'Here rscombo!MAXID is the last number. To show the next required number
  13.             'just add 1 to it and display. follow the same logic for others also
  14.             List1.AddItem rscombo!MAXID + 1
  15.  
  16.         Case "Maintenance Consumable"
  17.             rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic
  18.  
  19.         Case Else
  20.             rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic
  21.     End Select
  22. 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
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1.     rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'P' ", con, adOpenDynamic, adLockOptimistic
  2. 'OR
  3.     rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'U' ", con, adOpenDynamic, adLockOptimistic
Regards
Shaik Akthar
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 356
Reputation: firoz.raj is an unknown quantity at this point 
Solved Threads: 1
firoz.raj firoz.raj is offline Offline
Posting Whiz

Re: help(december 13,2008)

 
0
  #8
Dec 13th, 2008
•
•
•
•
Originally Posted by aktharshaik View Post
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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1.  
  2. Private Sub Combo1_Click()
  3.  
  4.     Dim nm As String
  5.     Set rscombo = New Recordset
  6.  
  7.     Select Case Combo1.Text
  8.         Case "Production Consumable"
  9.             'In this query replace 10 with a value equal to (size of your ITEM_CODE field - 1)
  10.             rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY", con, adOpenDynamic, adLockOptimistic
  11.             MsgBox (rscombo.Fields(0))
  12.             'Here rscombo!MAXID is the last number. To show the next required number
  13.             'just add 1 to it and display. follow the same logic for others also
  14.             List1.AddItem rscombo!MAXID + 1
  15.  
  16.         Case "Maintenance Consumable"
  17.             rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic
  18.  
  19.         Case Else
  20.             rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic
  21.     End Select
  22. 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
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1.     rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'P' ", con, adOpenDynamic, adLockOptimistic
  2. 'OR
  3.     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.
Attached Files
File Type: doc test.doc (26.0 KB, 1 views)
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 306
Reputation: aktharshaik is an unknown quantity at this point 
Solved Threads: 37
aktharshaik's Avatar
aktharshaik aktharshaik is offline Offline
Posting Whiz

Re: help

 
0
  #9
Dec 13th, 2008 
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
Last edited by aktharshaik; Dec 13th, 2008 at 2:12 am.
Regards
Shaik Akthar
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 306
Reputation: aktharshaik is an unknown quantity at this point 
Solved Threads: 37
aktharshaik's Avatar
aktharshaik aktharshaik is offline Offline
Posting Whiz

Re: help

 
0
  #10
Dec 13th, 2008
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()
Last edited by aktharshaik; Dec 13th, 2008 at 2:25 am.
Regards
Shaik Akthar
Reply With Quote Quick reply to this message  
Reply
1 2

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the Visual Basic 4 / 5 / 6 Forum


Views: 1079 | Replies: 12
Thread Tools Search this Thread



Tag cloud for Visual Basic 4 / 5 / 6
* 6 7 429 2007 access activex ado append application array banned basic beginner blackjack bmp box channel9 client coffeehouse college component connectionproblemusingvb6usingoledb creative data database datareport date delete designer dissertations dissertationthesis dissertationtopic error excel filter flex form game header icon ide image inboxinvb interaction key keypress listbox listview liveperson machine measure metadata movingranges nice noob number object open oracle os outlook password pause pdf picture pos print printer problem program query random range record refresh report save search size sort spectateswamp sql struct subroutine table tags textbox time timer variable variables vb vb6 vb6.0 vba visual visualbasic web windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2010 DaniWeb® LLC