Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2005
    Posts
    67

    Unanswered: Getting data input from form to use in a query

    Is it possible to get the data a user has entered into a form and use directly in a query? Let me explain a little:

    I have 3 forms. When the user has completed all 3, I need to produce an output text file.

    I want a small prepopulated form to open at the click of a button with the text entries from 2 fields (from the main form) populating these fields. These entries will be used within a query to select data based on these entries.

    Is this possible or is there an easier way?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    sounds like a good plan. In general, you can always access values from external objects (ie: forms, reports) by using fully qualified names. In this case:


    forms!yourForm!yourTextBox
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2005
    Posts
    178
    Code:
     
    .Open "SELECT *  FROM tblmytable WHERE tblmytable.myfield1 ='" & txtParameterField1.Text & "' AND tblmytable.myfield2 = '" & txtParameterField2.text & "'", Cnn, adOpenStatic, adLockOptimistic

  4. #4
    Join Date
    Dec 2005
    Posts
    67
    Hi - thanks for that, but it's not working - it's gone all red and is throwing up error message saying "expected end of statement". This is what I've got, can you see anything wrong with it??:

    strTable.Open "SELECT * FROM tblHeader WHERE tblHeader.RecptRef = ' "Forms!frmOutputData!txtOutputRR &" '_
    And tblHeader.ENO = "Forms!frmOutputData!txtENOOutput & " ' ", Cnn, adOpenStatic, adLockOptimistic"

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Code:
    strTable.Open "SELECT * FROM tblHeader WHERE tblHeader.RecptRef = ' " & Forms!frmOutputData!txtOutputRR & " '_
                  And tblHeader.ENO = '" & Forms!frmOutputData!txtENOOutput & " ' ", Cnn, adOpenStatic, adLockOptimistic"
    Watch your quotes and your "&"s.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by tasha123
    Hi - thanks for that, but it's not working - it's gone all red and is throwing up error message saying "expected end of statement". This is what I've got, can you see anything wrong with it??:

    strTable.Open "SELECT * FROM tblHeader WHERE tblHeader.RecptRef = ' "Forms!frmOutputData!txtOutputRR &" '_
    And tblHeader.ENO = "Forms!frmOutputData!txtENOOutput & " ' ", Cnn, adOpenStatic, adLockOptimistic"
    Yeah, you forgot the Ampersands infront of each word "Forms"

  7. #7
    Join Date
    Dec 2005
    Posts
    67

    SQL within VBA - quite fiddly!

    Thanks - but I got round that one by just writing it as one line and it worked and I was quite happy until I copied the second one which is much much longer.
    I copied it from within Access and it's coming up with errors. Is there a particular logic to the placement of ampersands and quotes and when do you use the singles and when do you use the doubles?

  8. #8
    Join Date
    Dec 2005
    Posts
    67
    Sorted that as well - replaced some double quotes with single ones and hey, ... it worked. Would still like to know the logic behind the quotes and ampersand as I still copied all the code on to one line. Thanks for all your help so far..... and Merry Christmas to all...

  9. #9
    Join Date
    Oct 2005
    Posts
    178
    You have to kinda memorize the sequence to encapsulate the text within your query statement with ' " & txt & " ' . Remember for a number it's different. It's like .. " Select * from tblmytable where tblNumberfield.inumber = " & numbervariable, Cnn, adOpenStatic, adLockOptimistic

  10. #10
    Join Date
    Dec 2005
    Posts
    67

    Desperate - VBA - sequentially write output of 2 queries to one file

    Hi I desperately need help as I'm soo close to my deadline. Thanks for all your help so far.
    Having run the output queries, I need to output them into one text file. When I did an append query, the second file's formats changed where a normal field was appended to a date field from the other table. I have now decided to output the files directly as text files, then try and merge them. I've managed to output them, but don't know the code to merge them. here is my export code
    DoCmd.TransferText acExportDelim, "TblOutput Export Specification", "tblOutput", _
    "K:\...\..\M..\T..\tblOutput.txt", True

    DoCmd.TransferText acExportDelim, "TblOutput2 Export Specification", "tblOutput2", _
    "K:\...\...\..\M\..\..\tblOutput2.txt", True

    Please help - thank you.

  11. #11
    Join Date
    Oct 2005
    Posts
    178
    YOu can use different methods but I see you already save them in txt format, use the Read method below then

    ' TO save --just showing how I would save the same text files
    Private Sub BtnSave_Click()
    Dim Text1 As String
    Dim Text2 As String

    Text1 = YOurText1
    Text2 = YOurText2

    Open App.Path & "\DataPath.txt" For Output As #1
    Write #1, Text1, Text2
    Close #1

    End Sub
    -------------
    'TO read

    Function GetText(TextPath as string) as string
    Open App.Path & TextPath For Input As #1
    Input #1, Text1, Text2
    Close #1
    GetText = Text1 & ":" & Text2
    End Function


    YOurDesireTExt = GetText("\Datapath.txt")


    SInce you already saved your text 1 and 2 to txt files and you know the path locations, you can just use the "To Read" code

    Does that make sense?
    Last edited by fredservillon; 12-23-05 at 14:04.

  12. #12
    Join Date
    Dec 2005
    Posts
    67
    Thanks for this - my version is playing up at the moment so I'll try using yours. Could you please explain this line - Open App.Path & "\DataPath.txt" For Output As #1. i.e. what would I be putting there - particulary in 'App.Path'. I'm only saving mine in D:\temp

  13. #13
    Join Date
    Dec 2005
    Posts
    67
    Hi - when I put Private Function within Private Sub it complains, how do i get round this?

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you don't/can't/shouldn't want to.


    private sub blah()
    private function santa() as string 'not allowed
    'somecode 'not allowed
    end function 'not allowed
    end sub

    what are you trying to do?

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by tasha123
    Hi - when I put Private Function within Private Sub it complains, how do i get round this?
    Sorry I missed the line "End Sub" I just corrected that one.

    BY the way, I used this currently in my application and I just change some text in it, and it works. I used it to store path location for whenever the user saves the path of file she/he wanted to save and when the user logs back in again and executes the application it fetches that file and use it in database connection codes.
    Last edited by fredservillon; 12-23-05 at 14:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •