Search Button in SubForm not Working

[Jen]

Dear All

I'm sorry to trouble you with such a basic question, but...(!)

I have a form of event details, and a subform within with person details. I
have put a command button in the subform to search (find a record). When I
click on it and try to search through the person details to find a name it
says no fields match the criteria etc.

For example, I have a record for John Smith ID 12345678, saved under John as
first name, Smith as surname etc etc. But when I select the first name and
search for John it says it's not there!

I have no idea what I'm doing wrong, but would really like this to work as
I'm setting it up for someone else to use who is even less competent with
Access than I am! It's obviously something really simple that I've missed.

Many thanks for any help.

 

[Wayne-I-M]

Hi Jen

Sorry to say this but I think that the general idea of your DB may be
improved by altering the basic structure.

The event does not exisit - people do. By this I mean that an event is
simply a link set of criteria - people, accomodation, flights, insurance,
etc, etc but a person is real and has "unique" properties ( unless you're a
twin of course <

If you have an event main formthen people can book/join the event which will
work fine until someone books more than 1 event. I understand that you "can"
allocate event ref (autonumbers) onto people's records but this is not the
best idea.

You need to have the "person's" details in the main form and then place the
events booked into a subform.

We have an event booking system with over 250,000 people on it - which is
NOT true - what we really have is 237,583 people (just checked) and some of
these have booked more than 1 event soe we have over 250,000 booking which
are just autonumber booking refs.

As you can see if you change the structure of your DB your will reduce the
number of records you need.

After you have change the structure - if you want - you can simply use a
combo to find the "person" and display the "event" details eather on a tab
(which is what we use - as this has the benifit of also enabling other linked
tabs with very little effort) or a subform.

 

[Jen]

Thanks for replying and I know what you're getting at, but there is a reason
why I have done it this way. The 'events' is the bigger and most important
of the two sections; and not all events have person details, only certain
ones. We have many events, but not so many people; also, one event can be
duplicated to many people, and although some people may have more than one
event, each event can be linked to many different people.

I hope this makes sense. If I were to organise the form the way you
suggested, it would mean repeating all the event details several times for
different people (and there are a LOT of details for each event!)


Many thanks for your suggestion though!

 

[Jen]

Thanks for replying Wayne-I-M

I understand why you suggest I organise the form differently, but there is a
reason why I have done it this way:

The 'events' section is by far the bigger, and also contains many different
details. The 'person' category is much smaller and contains fewer fields.

About half the events are not actually linked to any person at all. There
are only a small number of 'persons' many of which can be linked to several
different 'events'.

If I were to re-order the two sections, it would mean duplicating the event
details possibly many times for each different person, whereas my form means
I only have to input the 'event' details in once, and list the different
relevant 'persons'.

Most of the time we will need to search event details, but occasionally will
need to search by person, hence the question!

Thank you anyway for your help.

 

[Wayne-I-M]

Hi Jen

No worries you know your system and I don't so you must be right. The main
thing to remeber about access DB's is that (to cut out all the techincal
terms) rely on unique data and referecnes between them. A person is unique
as they can not be reporduced - hope that makes -sence - but an event is not
unique.

Anyway as I said you know you DB and I don't.

Back to your orignial question.

You have a form that lists events and a subform that lists people booked
onto the event - this may be lots or none. - Hope I'm right up to now - .

You need to open the main form in design view so you can see both the main
form and the sub form.

Click the outside of the subform and select Properties.
In the Data column you will see Link Child Fields and Link master Fields.

On both of your forms (main and sub) you should have a field of the same
dtata type number - text - etc. I would think it would be the Event ID
(which hopefully is an autonumber).
Don't forget that this Event ID field should also be included in the table
that holds the people's details (linking field)

Ensure that this Event ID field is in both forms and then select this field
for both the Linl Child Field and Link Master field in the subform's
properties box. Save the form and view it in Form View.

You should not have a list of all people booked onto the event that is
detailed in the main form.

 

[Wayne-I-M]

Typo

You should "now" have a list of all people booked onto the event that is
detailed in the main form.

 

[Wayne-I-M]

Just had a thought - my last post will work if you have the sub form set to
continous forms or datasheet but it will give you the results you want if you
have it set to single form (you will need to use the record selectors which
is a bit naff)

So - If you "do" have the subform set to single form

Open the subform in design view. Insert a new unbound combo box into your
subform header. Call this combobox - ComboSrch

Open the properties box for the combobox.

On the event column – AfterUpdate insert this small bit of code

Private Sub ComboSrch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Me![ComboSrch])
Me.Bookmark = rs.Bookmark
End Sub

In the Data Column
Row Source Type
Row Source Table/Query
SELECT ClientDetails.ClientID, ClientDetails.Client1stName.
ClientDetails.Surname FROM ClientDetails

Note – I have assumed that you have a table called ClientDetails
And that in this table you have fields called
ClientID
1stname
Surname
If you don’t then you will need to alter the row source


In the Event column on the Field Got Focus click build (code) and insert this

Private Sub ComboSrch_GotFocus()
Me.ComboSrch.DropDown
End Sub

This will force the combo to “drop down†when you select it.

In the Data column
Set the AutoExpand row to = Yes

This will let you type in the first few letters of a name and cut down the
list.

Save the form.

Try out the combo and let me know if there are any problems

Hope this helps