Creating a search form for multiple tables (1 Viewer)

[jlspickler]

Hi all,

Not only am I brand new to this forum (Hi!), I am also new to MS Access (I just took training on how to use it all day yesterday).

I am a biologist, not a computer programmer, so please use plain lingo since I am not able to understand much computer jargon!

Background on my Access problem:

I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:

Adult Banding Records
Nestling Banding Records
Historical Banding Records

The specific issue:

I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.

Thank you very much for any assistance, and please let me know if I was at all unclear!

-Jessica

 

[CJ_London]

You would find it a lot easier if you have just the one 'banding' table populated with your existing data and with one extra field which indicates the type - adult, nestling, historical. Then you only have to search one table

 

[jlspickler]

Hi CL_London,

Thank you for your response.

The only issue with that is the data we collect are very different between adults and nestlings, so while many fields overlap, there would be many other fields left blank depending on what kind of record is being entered. You also have to understand, the people who will be using this database might not be very computer savvy and will need something that "looks pretty" and is easy to use, hence the creation of forms for data entry/retrieval. They might be confused if many of the search fields are left blank. I am just trying to be as user-friendly as possible!

If this is the only way to go about making a single search form though, I will be more than happy to combine the banding records into one large table. I would just like to avoid it if possible!

-Jessica

 

[Cronk]

I agree with CJ - better one table with blank fields than the trouble of trying to combine results of multiple tables.

In the various forms, don't show the irrelevant fields and the users will not know whether the data is all coming from one table.

If you must, then you could use a union query (look it up) to combine results of multiple searches. But then what are you going to display? The disparate fields that you refer to in your response?

 

[jlspickler]

Ok, I just realized I can delete fields in the data entry forms I don't want to be displayed; that makes things much simpler!

After I make one giant table, how do I make a search form where I can enter the band number of the bird and all information related to that bird pops up? I know I can use the bar in the bottom of the forms I made for data entry and type the band number where "Search" appears. My only issue with that is I don't want anyone to be able to change what they have searched for. Is there a way to make the fields bound so the results pop up, but are unchangeable so the data doesn't get messed with?

Thanks again!

 

[CJ_London]

in your form properties - data tab, set allow edits to no - you may also want to set allow additions to no as well.

With regards searching you can also right click on a column and select various search options from there - or you need to build a search form, depends on your requirements

 

[jlspickler]

in your form properties - data tab, set allow edits to no - you may also want to set allow additions to no as well.

This worked perfectly, thank you


Now I am having problems with the search form itself.

I set up a simple form using the form wizard and included all fields, and included one unbound text box for the search box. I added a command button and put the following into the code builder:

Private Sub BandSearch_Click()
If IsNull(Text35) = False Then
Me.Recordset.FindFirst "[USFWS#]=" & Text35
Me!Text35 = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!Text35 = Null
End If
End If
End Sub

Every single time I search for something, it returns a "No records found" alert, even if I copy/paste an existing record from the table. The form simultaneously brings up the information from the first record in the table, which I also find odd since that is not what I was searching for.

Any ideas?

Thank you for your patience with me!

 

[CJ_London]

try

Private Sub BandSearch_Click()
    If nz(Text35) <>"" Then
        Me.filter="[USFWS#]=" & Text35
        Me.filteron=true
    End If
End Sub

I'm assuming that [USFWS#] is numeric and not text

Note, please use the code tags (# button) it will preserve the indenting and makes it easier to read

 

[jlspickler]

The code, as written, also didn't search.

The band numbers imported from excel as text, I am assuming because of the dashes (their format is ####-##### or ###-###### depending on banding sequence)

 

[CJ_London]

they will be text so modify this line to

Me.filter="[USFWS#]='" & Text35 & "'"

 

[jlspickler]

That worked! Thank you for your help through the process, CL_London!