search?

[AC-Dev]

Good day Developers! - again lol

Question:
is there anyway to create a form that will search EVERY table in my database and return the amount of records in whichever table it was in? if that is possible i want to create that and then have a way to direct the user to one of those selections.

 

[pbaldy]

The question implies a normalization issue, but you can use the DAO TableDef object to loop through the tables, using a DCount() within the loop to get a record count from each table. Searching on TableDef should turn up the basic code to loop them.

 

[AC-Dev]

The question implies a normalization issue, but you can use the DAO TableDef object to loop through the tables, using a DCount() within the loop to get a record count from each table. Searching on TableDef should turn up the basic code to loop them.

Thanks pbaldy. can you post a small sample for me please?

 

[pbaldy]

I don't happen to have one. Did your search not find anything?

 

[AC-Dev]

I don't happen to have one. Did your search not find anything?

not exactly. i searched the forms and came up short.
off to google!

 

[pbaldy]

Here are the basics of the loop:

  Dim db                      As DAO.Database
  Dim tdf                     As DAO.TableDef

  Set db = CurrentDb()

  For Each tdf In db.TableDefs
    Debug.Print tdf.Name 'this will be the name of the table
  Next

  Set db = Nothing
  Set tdf = Nothing

 

[AC-Dev]

so in your example, will this return the number of results along with the table name? or would i have to connect the two together myself?

 

[pbaldy]

No, you'd use the name in a DCount() formula to get the count.

 

[AC-Dev]

oki doki.
ill give it a try

 

[AC-Dev]

I had a new thought to anyone who would like to join in here

is there a way (in vba code) to search through an entire table? similar to the navigation bar's search method in a form?

i am doing something that i believe there is an easier way to do but i have no idea how to do it yet.

can anyone help me?

 

[AC-Dev]

No, you'd use the name in a DCount() formula to get the count.

im not sure um using the DCount() formula correctly.
here is my code

Private Sub btnSearch_Click()
  Dim db                      As DAO.Database
  Dim tdf                     As DAO.TableDef
  Dim varRecordCount(1, 44) As Variant
  Dim i As Integer
  
  Set db = CurrentDb()

  For Each tdf In db.TableDefs
    varRecordCount(0, i) = tdf.Name
    varRecordCount(1, i) = DCount("*", tdf.Name, "* ='" & Me.txtSearchString & "'")
    i = i + 1
  Next
 MsgBox varRecordCount(2, 0) & " " & varRecordCount(2, 1)
  Set db = Nothing
  Set tdf = Nothing

End Sub

anyone got and ideas?

 

[pbaldy]

If you just want a count you don't need a criteria. Try

DCount("*", tdf.Name)

If you're looking for a particular value in any field, you can't do it like that. You'd have to loop the Fields collection within each table. This again implies a design issue.

 

[AC-Dev]

The search box in the navigation in any form does exactly what i want to do, however i want to do it through every table. is there any way to call that function and that way i can manipulate it to work the way i want it to?

 

[YNWA]

Doesnt the search bar in a navigation bar search only search the table/query the form is linked to?

Thus going back to the design issue mentioned above.

 

[AC-Dev]

Doesnt the search bar in a navigation bar search only search the table/query the form is linked to?

Thus going back to the design issue mentioned above.

yes it does and that is essentially what i want to do - just for everything. Is there a way to view the code for that or call it when i need it?

unless someone has a better idea to search an entire database?

 

[YNWA]

yes it does and that is essentially what i want to do - just for everything. Is there a way to view the code for that or call it when i need it?

unless someone has a better idea to search an entire database?

I don't think the code is available for it but some sort of code to do similar will be out there.

The problem you will have is trying to get it to search through different tables, I am not sure it can be done.

 

[YNWA]

Does this do what you want: http://www.vb123.com.au/toolbox/09_access/dbsearch.htm

 

[AC-Dev]

I don't think the code is available for it but some sort of code to do similar will be out there.

The problem you will have is trying to get it to search through different tables, I am not sure it can be done.

well even if i can get it to search through one table will be excelent. My problem is trying to find a way to search based on criteria (user input) and go through the entire table, not just one field like a dlookup function.

I have created a for loop (Thanks to pbaldy) that goes through each table in the database and stores a value for each table. for every table it goes through, I can store the values in an multidimensional array which will then prompt the user to select a table via command button. when they click that button, I will run the same search, however this time i will query the records and not the count. the initial search it just to show the user how many ' hits' came up in each table.

maybe my question should be: how do i search for a value in a table in ANY field instead of Dlookups single field search?

here is my code

Private Sub btnSearch_Click()    
Dim db                      As DAO.Database    
Dim tdf                     As DAO.TableDef    
Dim varRecordCount(1, 44) As Variant   
Dim i As Integer      
  
Set db = CurrentDb()     

For Each tdf In db.TableDefs      
varRecordCount(0, i) = tdf.Name     
 varRecordCount(1, i) = DCount("*", tdf.Name, "* ='" & Me.txtSearchString & "'")      
i = i + 1    
Next    
Set db = Nothing   
 Set tdf = Nothing   

End Sub

 

[AC-Dev]

Does this do what you want: http://www.vb123.com.au/toolbox/09_access/dbsearch.htm

checking now

 

[pbaldy]

That does exactly what I said you were going to need to do; loop the fields collection within a loop of the tables.