Subform Data

I have three tables - one for personnel records, one for comp time earned and
one for comp time used. I created two subforms out of the comp time earned
and used and place them on a form. The two subforms shows whats in the tables
but what I want to do is add a combo box to the form that the two subforms
are on so that I can select records by individual. What is the best way to
do this - create a query or right from the tables ?

Can you break it down in beginners terms ?

Thanks - George

Hi George

Yes - run the form(s) from a query.

Next

Open your main form in design view and using the wizard create a new comb
box. Call it cboComboSearch. In this combo box you need to have 3 columns.
1st = CustomerID (this is the auto number that you should have on each
person’s record)
2nd = Surname
3rd = Firstname


Set the column widths to
0cm;2cm;2cm (you can alter this if they are not big enough)

Set the bound column to 1.

Once you have made the new combo save your form and try it out. It will not
do anything yet other than display records. but at least you'll know it doing
something.

Next – reopen the form in design view and click the new combo

Right click to open the properties box and select Event column
Select the AfterUpdare action and click build (that’s the … on the right)
Select code builder

You will see this


Private Sub cboComboSearch_AfterUpdate()

End Sub


You need to add these lines of code
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Me![cboComboSearch])
Me.Bookmark = rs.Bookmark

So it will look like this


Private Sub cboComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Me![cboComboSearch])
Me.Bookmark = rs.Bookmark
End Sub


Click save and close the code builder. If your ID (primary) field is NOT
called CustomerID you need to alter the code to what it is (leave the [ ] in
the same place and just type in the middle of them

Use the new combo to select a record and the form will auto populate all the
fields on the form INCLUDEING the subforms.

Providing that is that you have linked the subforms to the main (Parent/child)

Hope this was helpful


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



"George" wrote:

I have three tables - one for personnel records, one for comp time earned and
one for comp time used. I created two subforms out of the comp time earned
and used and place them on a form. The two subforms shows whats in the tables
but what I want to do is add a combo box to the form that the two subforms
are on so that I can select records by individual. What is the best way to
do this - create a query or right from the tables ?

Can you break it down in beginners terms ?

Thanks - George

Wayne - So far I have followed your steps but I get an error message
when I click on the combo box - This is the field that is highlighted when I
click on debug.

rs.FindFirst "[Address-ID] = " & Str(Me![cboComboSearch])

If I leave a name in the combo box and minimize the form and then open the
subforms by itself it opens with the info - the subforms just don't work when
both are on the main form.

I didn't follow your step to link the Main (Parent/Child) because I don't
know how.

Thanks - George

"Wayne-I-M" wrote:

Hi George

Yes - run the form(s) from a query.

Next

Open your main form in design view and using the wizard create a new comb
box. Call it cboComboSearch. In this combo box you need to have 3 columns.
1st = CustomerID (this is the auto number that you should have on each
person’s record)
2nd = Surname
3rd = Firstname


Set the column widths to
0cm;2cm;2cm (you can alter this if they are not big enough)

Set the bound column to 1.

Once you have made the new combo save your form and try it out. It will not
do anything yet other than display records. but at least you'll know it doing
something.

Next – reopen the form in design view and click the new combo

Right click to open the properties box and select Event column
Select the AfterUpdare action and click build (that’s the … on the right)
Select code builder

You will see this


Private Sub cboComboSearch_AfterUpdate()

End Sub


You need to add these lines of code
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Me![cboComboSearch])
Me.Bookmark = rs.Bookmark

So it will look like this


Private Sub cboComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Me![cboComboSearch])
Me.Bookmark = rs.Bookmark
End Sub


Click save and close the code builder. If your ID (primary) field is NOT
called CustomerID you need to alter the code to what it is (leave the [ ] in
the same place and just type in the middle of them

Use the new combo to select a record and the form will auto populate all the
fields on the form INCLUDEING the subforms.

Providing that is that you have linked the subforms to the main (Parent/child)

Hope this was helpful


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



"George" wrote:

I have three tables - one for personnel records, one for comp time earned and
one for comp time used. I created two subforms out of the comp time earned
and used and place them on a form. The two subforms shows whats in the tables
but what I want to do is add a combo box to the form that the two subforms
are on so that I can select records by individual. What is the best way to
do this - create a query or right from the tables ?

Can you break it down in beginners terms ?

Thanks - George

Hi again George

It sound like you have have a bit of a problem. No worries most things are
fixable.

As I cant see what you have in your form you’ll have to bear with me if I
get a few things wrong.

Going back to your 1st post

I have three tables - one for personnel records, one for comp time earned
and one for comp time used.

Do these tables have a relationship ??
By that I mean are there a “linking fields� in each table

You can check this by looking at the relationship window. If there is a
line between them then there is a relationship.

If it were me a would start with the main table which I think is your
Personnel Records (I will call this tbl PersonnelRecords – if you have called
it something else then you can alter the items below to whatever it is you
use.)

In tblPersonnelRecords there should be a “unique identifier�. This is a
field in your table that identies “this� record, as there may be more than
one John Smith so you can’t just use the name. This will normally be an
autonumber. I will call the PersonnelID.

In your other 2 tables (I have called them tblcomptime and tbltimeused) you
should have a field that will hold the same “type� of data as PersonnelID in
your table tblPersonnelRecords. I assume your have used an autonumber in
PersonnelID so this means you have corepondonding field in the other tow
tables. (you could call them refcompID and refusedID (Mind you, refused
could be a bit misleading ??)
Note I tend to put ref in front of linking child fields as I have zillions
of fields and I’m a bit dim and tend to forget what stuff is so this makes it
easy for me.

So assuming all this is done ???
Open the relationship window and ensure all 3 table are shown. Click and
hold the PersonnelID (in tblPersonnelRecords) and drag it across and put it
on top of the other 2 linking fields (one at a time). This will create the
relationship.

As you have said you are using a query - Next go your query and ensure that
each of the 3 tables are shown in the design grid (the bit at the top). You
should be able to see the relationships (lines) you have just made. If this
is ok – and all the fields you need are in the query close and save.

Next. Open the main form (I have called it frmPersonnelRecords) and ensure
that the primary field “is� there – this is the PersonnelID field. If it is
close the form and open it in design view.

Now comes the nifty bit. You need to ensure that your 2 subforms are both
there AND THAT THEY ARE LINKED.

To do this you need to link the Master field (PersonnelID) in the “main�
form and the child field (PersonnelID) in the first form and the other child
field on your second subform (PersonnelID ) . Yes they are all the same.
You say that all 3 forms are based on the same query so you MUST ensure the
PersonnelID in on all three forms (you can always set the visible to No if
you want to hide this field but they must be there.

To do this
Open the main form in design view
Click the “form� area of a subform (not the detail section – it’s the grey
bit at the bottom) and open the properties box. Select the Data column.
Look at the link child field and the link master field and ensure that they
both have the field in them they are meant. If not then simply click the
field and select from the dropdown list.

Save the form then view it and you see that the subforms are linked to the
main form.

I hope 8-)


Next. On your “Main form� you say you have a combo box (I have called it
cboComboSearch)
Right click to open the properties box and select Event column.
Select the AfterUpdare action and click build (that’s the … on the right)
Select code builder

You will see this


Private Sub cboComboSearch_AfterUpdate()

End Sub



You need to add these lines of code
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonnelID ] = " & Str(Me![cboComboSearch])
Me.Bookmark = rs.Bookmark

So it will look like this (don’t forget to alter PersonnelID if you have
used a different name for this field)


Private Sub cboComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonnelID] = " & Str(Me![cboComboSearch])
Me.Bookmark = rs.Bookmark
End Sub


Click save and close the code builder.

Use the new combo to select a record and the form will auto populate all the
fields on the form INCLUDEING the subforms.

“Providing� that is that you have linked the subforms to the main
(Parent/child)


Let me know if this is OK.

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.