locked
syntax error need help in vba in access (sql) RRS feed

  • Question

  • Option Compare Database

    Sub test()
    Dim test As Recordset

    Dim outputtest As Recordset

    Public Function sqltestt()

    Dim sqltest As String

    Dim inputtest As String

    inputtest = InputBox("enter search key")

    sqltest= select inputtest
             from testable 'testtable is a table where i have all data
             
    End Function


    all i want is to use inputbox in select is it possible
    Friday, April 27, 2018 11:06 AM

Answers

  • also appform doesn't show buttons or anything its empty i can see what i have in appform only from design mode

    Should look like this (after clicking on the Search Bar). You pick from the column name from the DropDown and enter the value in the TextBox to the right.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Davit13 Monday, May 21, 2018 6:28 AM
    Monday, May 14, 2018 2:37 PM

All replies

  • If you are using DAO:

    Dim db As DAO.Database Set db = CurrentDb Set test = db.OpenRecordset("SELECT * FROM testtable WHERE searchfield = '" & inputtest & "'") While Not test.EOF Debug.Print test.Fields("col1").Value

    Debug.Print test.Fields("col2").Value

    test.MoveNext Wend test.Close Set test = Nothing



    Paul ~~~~ Microsoft MVP (Visual Basic)




    Friday, April 27, 2018 12:39 PM
  • im doing this in ms access db 

    <tfoot></tfoot>
    TestTable
    CountryID Country Company Accinfo TypeOfAccInfo ItemName MoreDetails EmployeeName ItemPrice DateOfDoc BankName BankMoney CutMoney
    1 Georgia Processintgroup Expences OfficeExpences Chair WoodenChair Qeti $3.00 4/20/2018 TBC $10,000.00 $3.00
    1 Georgia Axistower Expences StuffExpences PC HP-Omen For Nino $1,000.00 4/19/2018 BOG $200,000.00 $10,000.00
    2 Italy Bravo Expences CarExpences Oil Porsch Bardia $30.00 4/19/2018 VTB $30,000.00 $30.00
    3 Iran Beheshtegomshodeh Asset HomeFurniture TV Home
    $100.00 4/24/2018 DR.Azizi TBC $200,000.00 $120.00

    This is my table and i want to make it work in this way: you have textbox and write what you want or have a drop down list of fields and when you choose  what you want macros or module (idk im rookie...) make it select that  like in code above where searchfilter=inputtest ( but couldn't figure it out what's missing... it opens a window of macros (to create because i don't have any) and even if i create its still not working
    in queries its working there is just select [fieldname] 

                                                                from testable



    • Edited by Davit13 Friday, April 27, 2018 1:04 PM
    Friday, April 27, 2018 12:54 PM
  • OK, so do you have a TextBox with the field you want to search and also a TextBox for the value being searched? You didn't post much code so I can't really tell what you want exactly.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 27, 2018 1:48 PM
  • hello i don't have anything and my question is i only have table i don't know how to connect table to code... in querrys i have select [table i want it to open] from test table (the table i have all info) and i want app to work that way someone choose field from drop box or with textbox they write which field they want to search (there may be more than one ) is it possible 
    Monday, April 30, 2018 7:02 AM
  • also i think the problem is with parameters of table some fields are short text some currency or date or number
    Monday, April 30, 2018 7:03 AM
  • also if i have 2 textbox can i use them like select *
                                                                   from db with all info

                                                                   where [textbox1]="textbox2"

    Monday, April 30, 2018 8:42 AM
  • buddy can you help me ? 
    Thursday, May 3, 2018 8:52 AM
  • buddy can you help me ? 

    I'm having trouble understanding what you are asking for. Do you have a picture of what you want the Form to look like?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 3, 2018 12:05 PM
  • I cant upload a picture because i'm not verified can you give me mail and i will sand it to mail?

    Thursday, May 3, 2018 12:56 PM
  • I cant upload a picture because i'm not verified can you give me mail and i will sand it to mail?


    No e-mail sorry. I would recommend posting to a site where it can be downloaded. That way other people can take a look as well.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 3, 2018 1:55 PM
  • try this https://files.fm/u/v8uncgnn name is prototype db
    Thursday, May 3, 2018 2:02 PM
  • try this https://files.fm/u/v8uncgnn name is prototype db

    So you have two Command Buttons (SearchBar and DisplayTable). What do you want to display when you click on them?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 3, 2018 2:50 PM
  • that is appform in app form i want to make a logic 
    select *

    from testable

    where [textbox1]="textbox2"


    and in testform
    when you enter what you want to open it show that fields/records

    Friday, May 4, 2018 11:34 AM
  • basically what i want to do is that i could get information from testtable from field names like country (from all countries or from specific like georgia and all records where country is georgia)
    or from records like if i know i have bought oil i want to search (it will be in ItemName) [ItemName]="oil" and it should give me that record

    display button is to open testtable to see all
    Friday, May 4, 2018 11:37 AM
  • im new to vba and access im more into sql and c# programing and cant do it now please help to make it <3
    Friday, May 4, 2018 11:38 AM
  • basically what i want to do is that i could get information from testtable from field names like country (from all countries or from specific like georgia and all records where country is georgia)
    or from records like if i know i have bought oil i want to search (it will be in ItemName) [ItemName]="oil" and it should give me that record

    display button is to open testtable to see all

    Let me take a look. I should be able to create an example that you can work with.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 4, 2018 1:39 PM
  • thank you
    Friday, May 4, 2018 2:06 PM
  • thank you

    Here is what I did. First I set the Record Source of AppForm to TestTable:

    Then I dragged fields on to the AppForm using the Field List:

    Then I added the following code to the Click Event of the search_bar Button:

    Private Sub search_bar_Click()
    
    Me.Filter = "[" & Me!texta.Value & "] = '" & Me.textb.Value & "'"
    Me.FilterOn = True
    
    End Sub

    When I place AppForm in Form View I enter the search data into the TextBox controls and click the SearchBar Button and the following is displayed (two rows found):

    Is this what you want to do? The Filter would need to be modified to work with different data types, such as Date and Currency, but that would be the next step.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 4, 2018 9:18 PM
  • thank you buddy the only question i have is that will this work if i want to search by 2 fields? like georgia(for country) and processintgroup(for company) 

    Monday, May 7, 2018 7:06 AM
  • and also can i download that file
    Monday, May 7, 2018 7:07 AM
  • and one more question is it possible to be done with drop-down list? like in second form where i have dropdown lists and search button 
    Monday, May 7, 2018 7:40 AM
  • thank you buddy the only question i have is that will this work if i want to search by 2 fields? like georgia(for country) and processintgroup(for company) 


    You would need to add another set of TextBox controls for a second set of fields, so yes it would be possible.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 7, 2018 12:16 PM
  • and one more question is it possible to be done with drop-down list? like in second form where i have dropdown lists and search button 

    Yes, it's possible. Let me work on that.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 7, 2018 12:17 PM
  • and also can i download that file

    I will upload after making additional changes.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 7, 2018 12:18 PM
  • thank you buddy you helped a lot really appreciate it  if possible can you make second form with drop-down lists work ? its connected to fields an i just want to have search based on it... 
    Monday, May 7, 2018 1:37 PM
  • and also can i download that file

    Here is the link to the database so you can download (it will only be temporary):

    https://1drv.ms/u/s!AkLRUZRgBixnitMiaZBVSAURYfE8cw

    I added a Search form. I needed to add a check for each column since the syntax in the SQL statement will be different depending upon the data type. You will find the code for the Search form in the btnSearch_Click event.

    The Search form is opened from the search_bar_Click event on the AppForm form.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 8, 2018 4:07 PM
  • Hello friend sorry for late answer ... thanks for help u did a great job <3 i have only one question have you fixed the drop-down list search form "testform" ? i think it doesn't work or i'm doing something wrong with it can you check?
    Monday, May 14, 2018 12:37 PM
  • also appform doesn't show buttons or anything its empty i can see what i have in appform only from design mode
    Monday, May 14, 2018 12:38 PM
  • also cant understand how filter works in search form.. it doesn't filter ...
    Monday, May 14, 2018 1:00 PM
  • Hello friend sorry for late answer ... thanks for help u did a great job <3 i have only one question have you fixed the drop-down list search form "testform" ? i think it doesn't work or i'm doing something wrong with it can you check?

    No I didn't do anything with TestForm. I created the Search form for AppForm.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 14, 2018 2:30 PM
  • also appform doesn't show buttons or anything its empty i can see what i have in appform only from design mode

    Should look like this (after clicking on the Search Bar). You pick from the column name from the DropDown and enter the value in the TextBox to the right.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Davit13 Monday, May 21, 2018 6:28 AM
    Monday, May 14, 2018 2:37 PM
  • is it possible to make it work like in testform? i mean in testform you can choose the field what you are searching for it may be 1 or as many as you want ... 

    i mean if it works it should search this way:
    <tfoot></tfoot>
    TestTable
    CountryID Country Company Accinfo TypeOfAccInfo ItemName MoreDetails EmployeeName ItemPrice DateOfDoc BankName BankMoney CutMoney
    1 Georgia Processintgroup Expences OfficeExpences Chair WoodenChair Qeti $3.00 4/20/2018 TBC $10,000.00 $3.00
    1 Georgia Axistower Expences StuffExpences PC HP-Omen For Nino $1,000.00 4/19/2018 BOG $200,000.00 $10,000.00
    2 Italy Bravo Expences CarExpences Oil Porsch Bardia $30.00 4/19/2018 VTB $30,000.00 $30.00
    3 Iran Beheshtegomshodeh Asset HomeFurniture TV Home
    $100.00 4/24/2018 DR.Azizi TBC $200,000.00 $120.00
      this is a table and in testform fileds as as many as fields in testform
    so when i fill info it will be like
    georgia      processintgroup   (if i want to see all accinfo i wont choose anything)  office expences (it will filter all info it may be asset or anything in accinfo but in type of acc info it will be officeexpences... and so on i think you got my point
    Wednesday, May 16, 2018 6:30 AM
  • also search form doesn't work... tried to download again but still not working... 
    Wednesday, May 16, 2018 6:34 AM