Search Form, Search mutliple tables (1 Viewer)

[tragik]

Hello, I have been trying to find a posting here or an example that could give me some guidance. I have 5 tables that has information in them, each table is a article type for my magazine archive and one for the magazine issue itself.

So say for example: you want to read all articles ( i am archiving 10 years worth of model building magazines, scanning the pages and hyperlinking them from local pc) pertaining to a p-51 (tblsubject) airplane in 1/48th (tblscale) scale from Revell (tblManufacture). I would like to search all articles and find all records that meet such criteria. I see examples of searching for data in one table but across many i cant find.. I tried to impliment an example with a queary that had all my tables in them.. what i got was a result with each table going sideways (make sense).

I am ok with having a result box for each article i guess.. but would REALLY love having them all in one list going vertical down. I may be asking for something impossible but would like to know if anyone has any idea how to go about that way. Now, FYI, other than being in each tbl there is nothing that says a certain article is a tblreview or tblbuilds (as in no field to mark the type of article it is).

I am still very new to VB and Access but not afraid to poke around and brake my test database.. so please keep that in mind..

Here is what each table looks like:

Here are my Constant tables that share the same data, each article table (tblreviews,tblinfo,tblbuilds,tblhowto) shares.

tblScale:
ID: unique ID
Scale: unique scale, no duplicates (ie: 1/48, 1/72 etc)

tblManufactures:
Manufacture_ID: unique ID
Manufacture: unique manufacture, no duplicates (ie: Revell, Tamaya etc)

tblSubject:
Subject_ID: unique ID
Subject: unique subject that will not duplicate (ie: p-51, p-38, etc)
-----------------------------------------------

tblMagazine:
Magazine_ID: unique id (this is how articles are linked to a magazine)
Magazine_Title: Name of magazine (linked from tblMagazine_Title)
Magazine_Month: Issue Month
Magazine_Year: Issue Year
Volume: Volume of magazine
Issue: Issue of Magazine
Cover_Image: hyperlink to a local image on pc
------------------------------------------------------------

Here are my Tables, These are the tables in need to search.

tblReviews:
Reviews_ID: unique id
Reviews_Subject: Subject of the article (ex: P-51, or P-38 etc)
Reviews_Scale: 1/48, 1/72
Reviews_KIt: number to kit, (ex: 46-545)
Reviews_Manufacture: tamaya, revell, etc..
Reviews_Magazine: this is what links the article to the tblMagazine unique ID
Reviews_Author: Author of article
Reviews_Image: hyperlink to local scanned image on pc

tblInfo:
Info_ID: Unique id
Info_subject: same as above
Info_Magazine: links to the magazine ID
Info_Author: same as above
Info_Image: same as above

tblBuilds:
Builds_ID: unique id
Builds_subject: same as above
Builds_Scale: 1/48, 1/72, etc
Builds_Manufacture: same as above
Builds_Kit_Number: number to the kit (ex: 4345-0)
Builds_author: same as above
Builds_Magazine: links to the magazine ID
Builds_Image: same as above

tblHow_To:
How_To_ID: unique ID
How_To_Title: this can be an open text of article (ie: god i am lost on building a search function across all article tables)
How_To_subject: same as above
How_To_scale: same as above
How_To_manufacture: same as above
How_To_author: same as above
How_To_Magazine: links to the unique ID of the magazine
How_To_Image: same as above

-----------------------
Thanks for reading and taking the time
sean.

 

[John Big Booty]

The cool search too discussed in this thread may help you.

 

[tragik]

The cool search too discussed in this thread may help you.

thanks John, will check it out after put the young one to sleep..

 

[neileg]

You should really only have one table with a field to identify the type of article. The search then becomes as easy as pie.

 

[tragik]

Neileg.. can I smack you? (not in a bad way, buy you a 1775 beer, great beer!).. you are sooooo right and I will take your suggestion.. so, could you give me some suggestions the best way to get all that data into one table? Now, i understand i will have to have a universal table and some articles will not utilize some fields..

So looking at my structure what would anyone suggest about getting that data onto one table? Copy paste? import? Please don't say start over..

 

[David R]

Try looking into Append Queries. Build your new table, append the existing records from one table at a time into the new table, adding a 'field' in your Append Query that is a constant for the new field.

ArticleTypeCode: "Chevrolet"

(or whatever, it's probably actually going to be a code or something, depending on your data)

I seem to remember that this dummy field should not be named exactly the same as the field you're trying to append to.

 

[tragik]

Try looking into Append Queries. Build your new table, append the existing records from one table at a time into the new table, adding a 'field' in your Append Query that is a constant for the new field.

ArticleTypeCode: "Chevrolet"

(or whatever, it's probably actually going to be a code or something, depending on your data)

I seem to remember that this dummy field should not be named exactly the same as the field you're trying to append to.

Thanks David, I will look into 'append queries' today.. will try and find a tutorial on that somewhere.. Thanks for giving me a 'name' to go after and research.

 

[David R]

Make sure you back up your database before you start moving stuff around, just in case!

Also, Append Queries: http://office.microsoft.com/en-us/access/HA100765261033.aspx

 

[tragik]

Make sure you back up your database before you start moving stuff around, just in case!

Also, Append Queries: http://office.microsoft.com/en-us/access/HA100765261033.aspx

Thanks David, heheh yea have several copies of my database. It is still somewhat small (23mb) but have more than enough records to not have to type them in over again.

 

[tragik]

ok, now i am getting frustrated.. i am trying to append a table as instructed.. but i have a table called tblBuilds and my new table; tblMainArticle

now, in a nut shell , when i preview the query i see my scale as correct (ex: 1/48, 1/72) but when i run the append part it changes to the UNIQUE ID.. (ex: 32, 40)..

Any idea's what i am doing wrong? or tell me what you need to see where i am jacking it up?

thanks.

 

[tragik]

UPDATE: so, here are my tables and settings.. ( i still get the same problem if i take my tblBuilds and "create new table" from the query).
tblScale (lists all possible scales)
ID: unique ID, auto number
Scale: Text (text box, indexed: no)

tblBuilds:
ID: unique ID, auto number
Builds_Scale: Number (indexed: no,)(Lookup: Combo box, row source type: Table/query, Row Source: SELECT [tblScale].ID, [tblScale].Scale FROM tblScale ORDER BY [ID] DESC; , Limit to List: yes, Allow Multiple Values: No)

I hope this helps some.

 

[tragik]

UPDATE: ok, here is what i did, came to me late last night in bed, i appended the data to an already working tbl. it worked.. so if i have 5 tbl's i took #1 and append it to #5.. it worked either way, so i am back on track.. thanks

 

[tragik]

Ok, so i got all my tables into one table now,

tblReviews:
Reviews_ID: unique id
Type: text (type of article it is, ex: Builds, Review, Info, How To)
Title: text (title of a how to article, ex: God i am lost on this code error)
Reviews_Subject: Subject of the article (ex: P-51, or P-38 etc)
Reviews_Scale: 1/48, 1/72
Reviews_KIt: number to kit, (ex: 46-545)
Reviews_Manufacture: tamaya, revell, etc..
Reviews_Magazine: this is what links the article to the tblMagazine unique ID
Reviews_Author: Author of article
Reviews_Image: hyperlink to local scanned image on pc

Besides the extra two fields (not with the name Reviews_*) there is no change.

Ok, so I have attached the search database i am trying to utilize. Here is my code.

I have commented out the 'date' parts because my form has no use of the date search. When I try and run my search it stops at "If Not IsNull(Me.Type) Then" and highlights the ME. To help I will include the original code.

Option Compare Database

Private Sub clearbutton_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearch"
End Sub

Private Sub Searchbutton_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.Type) Then
'Create Predicate
strWhere = strWhere & " AND " & "tblReviews.Type = " & Me.Type & ""
End If

' If Opened By
If Not IsNull(Me.Subject) Then
'Add the predicate
strWhere = strWhere & " AND " & "tblReviews.Reviews_Subject = " & Me.Subject & ""
End If

' If Status
If Nz(Me.Scale) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "tblReviews.Reviews_Scale = '" & Me.Scale & "'"
End If

' If Category
If Nz(Me.Manufacture) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "tblReviews.Reviews_Manufacture = '" & Me.Manufacture & "'"
End If

' If Priority
If Nz(Me.MagID) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "tblReviews.Reviews_Magaizne_ID = '" & Me.MagID & "'"
End If


' If Opened Date From
'If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
' strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " & GetDateFilter(Me.OpenedDateFrom)
' ElseIf Nz(Me.OpenedDateFrom) <> "" Then
' strError = cInvalidDateError
'End If

' If Opened Date To
' If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
' strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " & GetDateFilter(Me.OpenedDateTo)
' ElseIf Nz(Me.OpenedDateTo) <> "" Then
' strError = cInvalidDateError
' End If

' If Due Date From
'If IsDate(Me.DueDateFrom) Then
' Add it to the predicate - exact
' strWhere = strWhere & " AND " & "Issues.[Due Date] >= " & GetDateFilter(Me.DueDateFrom)
'ElseIf Nz(Me.DueDateFrom) <> "" Then
' strError = cInvalidDateError
'End If

' If Due Date To
'If IsDate(Me.DueDateTo) Then
' Add it to the predicate - exact
' strWhere = strWhere & " AND " & "Issues.[Due Date] <= " & GetDateFilter(Me.DueDateTo)
'ElseIf Nz(Me.DueDateTo) <> "" Then
' strError = cInvalidDateError
'End If

' If Title
If Nz(Me.keyword) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblReviews.Title Like '*" & Me.keyword & "*'"
End If

' If SerialNumber
If Nz(Me.author) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblReviews.Reviews_Author Like '*" & Me.author & "*'"
End If

' If CaseNumber
If Nz(Me.kit) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblReviews.Reviews_Kit_Number Like '*" & Me.kit & "*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.frmSearch.Form.Filter = strWhere
Me.frmSearch.Form.FilterOn = True
End If
End Sub

original code

Option Compare Database
Option Explicit

Private Sub Clear_Click()
DoCmd.Close
DoCmd.OpenForm "Search Issues"
End Sub

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Assigned To] = " & Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = " & Me.OpenedBy & ""
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Status = '" & Me.Status & "'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category & "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority & "'"
End If


' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " & GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " & GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Due Date From
If IsDate(Me.DueDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] >= " & GetDateFilter(Me.DueDateFrom)
ElseIf Nz(Me.DueDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Due Date To
If IsDate(Me.DueDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] <= " & GetDateFilter(Me.DueDateTo)
ElseIf Nz(Me.DueDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title & "*'"
End If

' If SerialNumber
If Nz(Me.SerialNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.SerialNumber Like '*" & Me.SerialNumber & "*'"
End If

' If CaseNumber
If Nz(Me.CaseNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.CaseNumber Like '*" & Me.CaseNumber & "*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Private Sub Serial_Number_BeforeUpdate(Cancel As Integer)

End Sub

Hope I presented the code correctly (putting in quotes)

 

[tragik]

question.. in my code am i missing something like Me!? or something.. i am trying to research why my Me. is failing..

 

[neileg]

Me.Type is looking for a control on the current form called Type. As you have a field called Type the form wizard would have created a control called Type. If you have built the form yourself you may not have called the control Type. This would cause the problem you have.

It's actually a bad idea to have a control named the same as a field. Why does the Microsoft wizard lead you into bad practices? You tell me.

 

[tragik]

Me.Type is looking for a control on the current form called Type. As you have a field called Type the form wizard would have created a control called Type. If you have built the form yourself you may not have called the control Type. This would cause the problem you have.

It's actually a bad idea to have a control named the same as a field. Why does the Microsoft wizard lead you into bad practices? You tell me.

Thanks Neileg.. unfortunately i didnt use the wizard and this was all my fault.. could you direct me how to resolve my problem and rename the control.. or could you direct me to a good read-me on what you mean? When i get to work i will try an google what you mean about the 'control'.

thanks for replying and looking the code i botched.. lol

 

[neileg]

Control is the generic name for an element of a form that holds data. So a text box or a combo box are both controls. All controls have a name that you can see in their property sheet on the Other tab. Me is the shorthand used to refer to the current object, in this case your form. So if the code is stopping at If Not IsNull(Me.Type) this suggests Access can't find Me.Type. It can't be Me because that's your form and the code is in the form so it must be that it can't find the control called Type.

 

[tragik]

Ok, so I understand what you mean about the 'controls' and I have fixed that issue. I also had an issue where one of my fields was called combo09 and not a proper name. Changed it to cmbType as an example.

Now if i choose an option from my subject (which is a combo from the tblSubject) and hit search i get a error.

run-time error '3075'
sytax-error (missing operator) in quiery expression "1=1 AND tblReviews.Reviews_Subject - 1932 Ford Deuce Coupe'.

(now, i tried to search for that car in my articles)

I am attaching my database to maybe help where i am going wrong.. Thanks.

*actually the size is 1.6 mb (zipped) and wont upload due to size.. here is my server that is sharing this folder.. You will see a folder called "backup", double click it and it will show you the contents. inside that you will see a "copy of magazines.zip" or "copy of magazines.mdb" both of these are what i am working now (test) and where i am getting these errors. you should see a download button next to the file and you can download it.

https://spideroak.com/browse/share/MJURGENSEN/2007

 

[neileg]

Sorry, our firewall won't allow downloads.

Are you sure the message is "tblReviews.Reviews_Subject - 1932 Ford Deuce Coupe" and not "tblReviews.Reviews_Subject = 1932 Ford Deuce Coupe"?

 

[tragik]

Sorry, our firewall won't allow downloads.

Are you sure the message is "tblReviews.Reviews_Subject - 1932 Ford Deuce Coupe" and not "tblReviews.Reviews_Subject = 1932 Ford Deuce Coupe"?

you are correct.. sorry, I am remote into my pc at home (so i can work on it while at work, shhhh lol) and the VNC screen made it look like a - not a =.. so, you are correct " _Subject = 1932 Ford "

to add more info: if i debug it highlights the row " Me.frmSearchAllArticles.Form.Filter = strWhere "

'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.frmSearchAllArticles.Form.Filter = strWhere
Me.frmSearchAllArticles.Form.FilterOn = True
End If

Ok here is all the issues:
Here are all my search fields.
keyword: unbound (name: keyword, should search the entire table for anything matching)
Type: unbound combo box ( row source: Builds";"How To";"Info";"Review", name: cmbType)
Subject: unbound combo box (row source: SELECT [tblSubject].[Subject] FROM tblSubject ORDER BY [Subject]; , name: cmbSubject)
Scale: unbound combo box (row source: SELECT [tblScale].[ID], [tblScale].[Scale] FROM tblScale ORDER BY [Scale]; , name: cmbScale)
Kit Number: unbound text box (name: kit, no row source)
Manufacture: unbound combo box (row source:SELECT [tblManufactures].[Manufacture] FROM tblManufactures ORDER BY [Manufacture]; , name: cmbManufacture)
Magazine_ID: unbound combo box (row source: SELECT [tblMagazine].[Magazine_ID] FROM tblMagazine ORDER BY [Magazine_ID]; ,name: cmbMagID)
Author: unbound text box (no row source, name: author)

errors:
Keyword: type in p-51 (type of airplane) and the frmSearchAllArticles opens at the footer, but empty.. (* found out this will search just the tblReviews.Title field, i want it to search the whole table and every field)
Type: choose builds from drop down, and 'enter parameter value' opens, builds.. not what it should do?
subject: see above
Scale: 1/48 scale from the drop down and get run-time error 3464, debug: (highlights) Me.frmSearchAllArticles.Form.FilterOn = True
Kit number: just like keyword
Manufacture: woohoo, this one seems to be working??? woohoo yea!
Magazine_ID: does the same as Type; this should pull all articles from a magazines unique ID, like issue 1
Author: searched 'mike' and it worked!!! what the????
*take that 'mike' and search under keyword, comes up blank.. should pull 'anything' in the table called 'mike'

hope this helps with the chaos i have created..lol.. now that i have tried each one, not sure why two of them work..