Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    22

    Exclamation Answered: Help! 2 Problems where I need help

    Hey guys,
    I am looking for some help on 2 things.

    The premise:

    I have a database for a prototype for a tool which would allow folks to filter thru bunch of data. There is a single table with all the entries. There are about 17/18 columns in the data set. Out of these i would like the ability to search or filter on almost all of these options.

    Here is the database structure in excel:

    Click image for larger version. 

Name:	Database Structure.PNG 
Views:	2 
Size:	33.3 KB 
ID:	17079

    Now in here what you see as highlighted columns are the searchable columns.

    Issue 1:

    I would like to create form where i have multiple checkboxes which would allow for multiple selections. I would like then take the inputs from the these checkboxes and put them as the "where" condition of the query. Like this:

    Click image for larger version. 

Name:	Checkboxes Samples.PNG 
Views:	2 
Size:	4.3 KB 
ID:	17080

    The above is just a sample of what i like to build out further with those columns reflecting options as checkboxes. When the user hits "Run Query" i would like to to build a "select" statement with the options selected as "where" condition.

    I need help in generating such a query at run time.

    Issue 2

    I started to play around the above mentioned issue and came up with the following code in VBA but i keep getting this error message "Too few parameters. Expected 1". The code is copied below:

    Code:
    Private Sub Command99_Click()
    Dim gblchk, emchk, apchk, nachk1, latchk1, sqlstr As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rsQuery As DAO.Recordset
    
    
    
    If GlobalChk.Value = True Then
        glbchk = "Global"
    End If
    If EMEAChk.Value = True Then
        emchk = "EMEA"
    End If
    If APACChk.Value = True Then
        apchk = "APAC"
    End If
    If nachk.Value = True Then
        nachk1 = "NA"
    End If
    If latchk.Value = True Then
        latchk1 = "LatAm"
    End If
    
    'sqlstr = "SELECT * FROM [RefTbl] WHERE [RefTbl.RefRegion] = '" & emchk & "'"
    'sqlstr = "SELECT * FROM RefTbl WHERE RefTbl.RefRegion = "EMEA";"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("RefTbl", dbOpenTable)
    
    If emchk <> "" Then
        
        MsgBox "Running"
        Set rst = db.OpenRecordset("SELECT * FROM [RefTbl] WHERE [RefTbl.RefRegion] = " & "" & emchk, dbOpenDynaset, dbReadOnly)
        
    Else
        MsgBox "not running"
    End If
    
    End Sub
    I cant seem to figure out where the problem is coming from. I ran just the SQL and it worked fine PLEASE HELP!

    Thank you in advance!
    Attached Thumbnails Attached Thumbnails Database Structure.PNG  

  2. Best Answer
    Posted by healdem

    "read up[ on normalisation
    read up om database design

    if you are unable to discover useful references heres a couple
    http://r937.com/relational.html
    also read tony marstons site re databses. I can't find the reference at a quick glance so you will have to plough through it, enjoy the learning process"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so you have got your initial data sample
    work throught the process of normalisation...

    BEFORE doing form or query design
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Jan 2013
    Posts
    22
    Thank Healdem,

    Could you clarify a bit more?

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    read up[ on normalisation
    read up om database design

    if you are unable to discover useful references heres a couple
    http://r937.com/relational.html
    also read tony marstons site re databses. I can't find the reference at a quick glance so you will have to plough through it, enjoy the learning process
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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