Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 21st, 2006, 08:08 AM
Registered User
 
Join Date: Jul 2006
Location: hyderabad, andhra pradesh, India.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sparsh2010
Default Not able to pass the combo box value to SQL query

hi Friends i am new bie in VBA ( MS access )

I have created a program which reads the text file then creates the csv file and creates a access databse file and then do some query

There are two forms and one module used in project - the form 1 executes the call for a module ( which reads the text file and creates a csv file ) then back to form 1 then form 1 imports the data from csv file to access databse file then invokes the VBquestion asking user that if he wants to do query if selected " yes " then it invokes another form in which user has to select the value from combo box and then the project should fetch the record

Problem - when starting from form1 and then invoking form2 then running the query " it asks me for the value " so i need to input the value even after i have selected from combo box
but when starting from form2 if i select the value in combox box , it runs the query

below is given code

Form 1
( this part reads the text file and creates a csv file )

Private Sub Read_Next_Click()

    Call Parse_Employee_File(strdir)

End Sub

Form 1

( This part imports the data from csv to access database file and invokes the form 2 )

Private Sub Command19_Click()
    Dim strSpec, strTable, strFile As String
    Dim intLawver As Long

    strSpec = "Employee Link Specification"
    strTable = "Employeedb"
    strFile = strdir & "\Employee" & ".csv"
    Debug.Print "Importing " & strFile

    DoCmd.TransferText acImportDelim, strSpec, strTable, strFile
    Debug.Print strFile & " has been imported"

    MsgBox "File import completed"

    iReply = MsgBox("Do you want to execute the query", vbQuestion + vbYesNo)

    If (iReply = vbYes) Then
       Form_Form2.Visible = True
    End If

End Sub

Form 2

Private Sub Combo0_Change()

      Dim InputID As String
      InputID = Form_Form2.Combo0.SelText

      If InputID <> "" Then
        MsgBox ("you have selected -" & InputID)
      End If

       strsql = "query1"
       DoCmd.SetWarnings False
       DoCmd.OpenQuery strsql
       Debug.Print "Check"

End Sub


The SQL query wrritten is

SELECT employeedb.ID, employeedb.Fstname, employeedb.LStname, employeedb.Number, employeedb.Address
FROM employeedb
WHERE (employeedb.ID=forms!form_form2!combo0);

Can anybody please tell me why i am not able to pass the value from form 2 to SQL query please suggest whereever i have to make change

  #2 (permalink)  
Old July 25th, 2006, 03:48 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

"SELECT employeedb.ID, employeedb.Fstname, employeedb.LStname, employeedb.Number, employeedb.Address
FROM employeedb
WHERE employeedb.ID = '" & forms!form_form2!combo0 & "';"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #3 (permalink)  
Old July 26th, 2006, 05:31 AM
Registered User
 
Join Date: Jul 2006
Location: hyderabad, andhra pradesh, India.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sparsh2010
Default

hello Greg Serrano

   Thanks for the reply to my problem, the problem has changed according to a law " the number of bugs remains constant in a software they just change one form to another "

     well now starting from form1 when i m invoking form 2 and after selecting the record it does opens the SQL datasheet view but it is blank, the query i used is

SELECT employeedb.ID, employeedb.Fstname, employeedb.LStname, employeedb.Number, employeedb.Address
FROM employeedb
WHERE employeedb.ID='" & forms!form_form2!combo0 & "';

Thanks and Reagrds
Pramod
  #4 (permalink)  
Old July 26th, 2006, 07:10 AM
Registered User
 
Join Date: Jul 2006
Location: hyderabad, andhra pradesh, India.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sparsh2010
Default

 hello Greg Serrano

    I figured out the problem now the problem is solved , i have just changed the method of invoking form2 by form 1

previously it was

    If (iReply = vbYes) Then
       Form_Form2.Visible = True
    End If

Now it is

    If (iReply = vbYes) Then
       DoCmd.OpenForm "Form2"
    End If

SQL query is same as

SELECT employeedb.ID, employeedb.Fstname, employeedb.LStname, employeedb.Number, employeedb.Address
FROM employeedb
WHERE (employeedb.ID=forms!form2!combo0);

and its working fine

Thanks and Regards



Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
Query based on combo box selection help Elain Access 1 January 3rd, 2006 11:33 PM
Combo Box Parameter Query stevewardau Access 3 June 24th, 2004 08:44 PM
Combo Box Parameter Query stevewardau Access VBA 2 June 24th, 2004 06:32 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.