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 <
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.