Jaybird's Brain has left the building

[Guest]

I've been burning the candle at both ends lately, and now I've found that I
can't think clearly about this problem. I could use some input... What I've
got is a form that logs all the order items that are to go into a furnace.
This is called [HT Load Info]. It logs all the information about the load as
a whole to a table called [HT Load Info]. Specific information about the
items in the load come from a continuous subform that is based on two tables
called [tblPartNumbers] and [tblOrderDetails]. The link between the main
form and the subform is a field called [Load Number] which is the primary key
for the table [HT Load Info]. So, by design, each record of the table [HT
Load Info] is a new load. And all of the items associated with that load are
to be stored in the table [tblOrderDetails] referenced by the field [Load
Number]. Makes sense to me, in my foggy logic. However, I can't seem to
figure out how to pass the load number from the main form to the subform
without errors. Presumably, once the load number is established in the main
form, I should be able to pick out the load items in the subform, and the
load number will be passed to those items programatically... Or so I thought
in my quieter moments. Now, the time has come to put the thing together (I
have people waiting on this project) and I find that I'm a little sketchy
about the important particulars, and I'm too brain fried to come up with a
solution on my own. Does anyone have ideas?

 

[Douglas J. Steele]

When you add a form as a subform, you're asked to provide LinkChildFields
and LinkMasterFields. Once you've done that, Access takes care of synching
the two.

 

[Guest]

Thanks, Doug. Yeah, I'm aware of that... But this field hasn't been
assigned a value yet. I wonder if I haven't conceived of this wrong... The
way I have it set up, the query behind the subform (based on tblPartNumber
and tblOrderDetails) is linked to my mainform by the field [Load Number].
However, this field (from the table tblOrderDetails) is blank until I fill it
in with a value. I was hoping to pass on a value from the main form as I
make my selections for the items in the load in the subform, but nothing I've
tried is doing the trick.
--
Why are you asking me? I dont know what Im doing!

Jaybird

When you add a form as a subform, you're asked to provide LinkChildFields
and LinkMasterFields. Once you've done that, Access takes care of synching
the two.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've been burning the candle at both ends lately, and now I've found that
I
can't think clearly about this problem. I could use some input... What
I've
got is a form that logs all the order items that are to go into a furnace.
This is called [HT Load Info]. It logs all the information about the load
as
a whole to a table called [HT Load Info]. Specific information about the
items in the load come from a continuous subform that is based on two
tables
called [tblPartNumbers] and [tblOrderDetails]. The link between the main
form and the subform is a field called [Load Number] which is the primary
key
for the table [HT Load Info]. So, by design, each record of the table [HT
Load Info] is a new load. And all of the items associated with that load
are
to be stored in the table [tblOrderDetails] referenced by the field [Load
Number]. Makes sense to me, in my foggy logic. However, I can't seem to
figure out how to pass the load number from the main form to the subform
without errors. Presumably, once the load number is established in the
main
form, I should be able to pick out the load items in the subform, and the
load number will be passed to those items programatically... Or so I
thought
in my quieter moments. Now, the time has come to put the thing together
(I
have people waiting on this project) and I find that I'm a little sketchy
about the important particulars, and I'm too brain fried to come up with a
solution on my own. Does anyone have ideas?

 

[Douglas J. Steele]

Assuming you've got the load number as a field in the RecordSource of the
form being used as a subform, getting a value for it in the parent form
should automatically populate the field in the new record on the subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug. Yeah, I'm aware of that... But this field hasn't been
assigned a value yet. I wonder if I haven't conceived of this wrong...
The
way I have it set up, the query behind the subform (based on tblPartNumber
and tblOrderDetails) is linked to my mainform by the field [Load Number].
However, this field (from the table tblOrderDetails) is blank until I fill
it
in with a value. I was hoping to pass on a value from the main form as I
make my selections for the items in the load in the subform, but nothing
I've
tried is doing the trick.
--
Why are you asking me? I dont know what Im doing!

Jaybird

When you add a form as a subform, you're asked to provide LinkChildFields
and LinkMasterFields. Once you've done that, Access takes care of
synching
the two.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've been burning the candle at both ends lately, and now I've found
that
I
can't think clearly about this problem. I could use some input...
What
I've
got is a form that logs all the order items that are to go into a
furnace.
This is called [HT Load Info]. It logs all the information about the
load
as
a whole to a table called [HT Load Info]. Specific information about
the
items in the load come from a continuous subform that is based on two
tables
called [tblPartNumbers] and [tblOrderDetails]. The link between the
main
form and the subform is a field called [Load Number] which is the
primary
key
for the table [HT Load Info]. So, by design, each record of the table
[HT
Load Info] is a new load. And all of the items associated with that
load
are
to be stored in the table [tblOrderDetails] referenced by the field
[Load
Number]. Makes sense to me, in my foggy logic. However, I can't seem
to
figure out how to pass the load number from the main form to the
subform
without errors. Presumably, once the load number is established in the
main
form, I should be able to pick out the load items in the subform, and
the
load number will be passed to those items programatically... Or so I
thought
in my quieter moments. Now, the time has come to put the thing
together
(I
have people waiting on this project) and I find that I'm a little
sketchy
about the important particulars, and I'm too brain fried to come up
with a
solution on my own. Does anyone have ideas?

 

[Guest]

Doug, I actually used a default value for the textbox on the subform to
populate the [Load Number] from the mainform. Making this field visible on
the subform, I can see that it is working. However, I cannot select the
lineitem from the combo I created in the subform. So, what I thought was one
problem is actually another. I'm not good at working with recordset clones,
so I counted on the wizard to set up the combobox for me. I guess I must
have changed something that is preventing the recordset from being updated.
I'll try removing the combobox and re-creating it with the wizard and see
where that gets me.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Assuming you've got the load number as a field in the RecordSource of the
form being used as a subform, getting a value for it in the parent form
should automatically populate the field in the new record on the subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug. Yeah, I'm aware of that... But this field hasn't been
assigned a value yet. I wonder if I haven't conceived of this wrong...
The
way I have it set up, the query behind the subform (based on tblPartNumber
and tblOrderDetails) is linked to my mainform by the field [Load Number].
However, this field (from the table tblOrderDetails) is blank until I fill
it
in with a value. I was hoping to pass on a value from the main form as I
make my selections for the items in the load in the subform, but nothing
I've
tried is doing the trick.
--
Why are you asking me? I dont know what Im doing!

Jaybird

When you add a form as a subform, you're asked to provide LinkChildFields
and LinkMasterFields. Once you've done that, Access takes care of
synching
the two.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Jaybird" <UMUhtq5013> wrote in message
I've been burning the candle at both ends lately, and now I've found
that
I
can't think clearly about this problem. I could use some input...
What
I've
got is a form that logs all the order items that are to go into a
furnace.
This is called [HT Load Info]. It logs all the information about the
load
as
a whole to a table called [HT Load Info]. Specific information about
the
items in the load come from a continuous subform that is based on two
tables
called [tblPartNumbers] and [tblOrderDetails]. The link between the
main
form and the subform is a field called [Load Number] which is the
primary
key
for the table [HT Load Info]. So, by design, each record of the table
[HT
Load Info] is a new load. And all of the items associated with that
load
are
to be stored in the table [tblOrderDetails] referenced by the field
[Load
Number]. Makes sense to me, in my foggy logic. However, I can't seem
to
figure out how to pass the load number from the main form to the
subform
without errors. Presumably, once the load number is established in the
main
form, I should be able to pick out the load items in the subform, and
the
load number will be passed to those items programatically... Or so I
thought
in my quieter moments. Now, the time has come to put the thing
together
(I
have people waiting on this project) and I find that I'm a little
sketchy
about the important particulars, and I'm too brain fried to come up
with a
solution on my own. Does anyone have ideas?

 

[Guest]

Nope. Can't get it to work. Something about the way I'm doing things won't
allow me to select any records using my combobox. The query behind the
subform is fully updatable. The rowsource for the combobox is set up to look
through the same recordset. Can't figure out what the deal is.

 

[Guest]

I've used the wizard 3 times to recreate the combobox that selects the
records in the recordset. I double checked to make sure that the recordset
in the code is the same as the recordset in the rowsource, and also that the
name of the control isn't getting confused with the controlsource for the
control. It looks good. The problem must be something more elementary.
Could somebody please baby-walk me through this? I've really got to have this
finished today, if possible. (Not that this is your problem, or anything...)
But please... Pretty please?

 

[Guest]

Okay, when I make the combobox unbound, the records are selected, but not
saved to the table... What the heck? Help.

 

[Douglas J. Steele]

That makes sense.

When a combo box is bound and you select another value, you're changing the
previous value that was contained in the field to which the combo box is
bound.

It's rare (if ever done) to use a bound combo box for navigation purposes.

 

[John W. Vinson]

Doug, I actually used a default value for the textbox on the subform to
populate the [Load Number] from the mainform. Making this field visible on
the subform, I can see that it is working. However, I cannot select the
lineitem from the combo I created in the subform. So, what I thought was one
problem is actually another. I'm not good at working with recordset clones,
so I counted on the wizard to set up the combobox for me. I guess I must
have changed something that is preventing the recordset from being updated.
I'll try removing the combobox and re-creating it with the wizard and see
where that gets me.

You're making the problem much harder than it needs to be.

You do not need a default value for the load number. You do not need any code
to set the load number. You do not need a recordsetclone. You don't need
ANYTHING to populate the load number in the subform other than the master and
child link fields!

The way they work is to filter the subform to show only those detail records
corresponding to the main form's load number, *and* to automatically fill in
the value in the Master Link Field (the load number) into the Child Link Field
(the foreign key load number in the detail table). It's not actually necessary
to display either field, and if you're using an autonumber you usually
wouldn't.

It sounds like you're trying to manually do things that Access does for you
automatically!

John W. Vinson [MVP]

 

[Guest]

Perhaps I can appeal to your superior knowledge of the subject matter to
interpret what it is I'm trying to do? I've created a version of this form
that creates a list of load items, but each item on the list behaves like its
own record on the mainform. I figured that a better way to go might be to
have the mainform based on a table that only has information regarding the
load as a whole. The subform is to include the lineitems from the load. The
Load Number would link them together. I figured that I should be able to
pick the items from a combobox and have all of the items I pick be associated
with that Load Number. That way, each record on the mainform would be a
different Load Number, and with different lineitems. I'm stumbling over all
kinds of basic things, here. Like, not realizing that the search box has to
be unbound. To get around this, I created a hidden textbox with the index I
wanted to include. Now the thing doesn't work at all. The results of my
search don't update to the tblHTDetails table. I'm guessing that this is why
the subform won't allow me to enter more than one record, even though it's
set up as a continuous subform. THAT'S what I need it to do... select a
list of line items and allow them to be saved to the tblHTDetails table.

 

[Douglas J. Steele]

Did John's answer resolve things?

 

[Guest]

I definately agree with you... Making things harder than they need to be
seems to be a trait I inherited from my father. I'm missing important
details here. I usually create the form first ('cause I'm a formatting snob,
and don't like the way the wizard does it, that's why) and then drag it over
to my form, then set the master and child links. If the table or query that
my subform has no matching records, I get nothing in my subform. This is
what I would expect. Go to add new record and add your data and it matches
the current link data in the mainform automatically. (I'd like to be able to
set my own link data, however). Okay, so maybe we're talking two dialects of
the same language. I guess my issue is that I'm not recognizing the real
problem or describing it at all well. I have, as the underlying query for my
subform (sbfHeatTreat) a fully updatable query called qryHTDetails. With
it, I can fully populate all the fields with information about the load
lineitems by using the column property and my combobox [txtOrderEntry]. It
also allows me to fill in the fields that need updating. Or, at least,
that's the way it's supposed to work. I can't get it to update the
underlying table. It's driving me crazy! Additionally, it won't go to the
new record. I wonder if the row source to my combobox doesn't contain the
unique identifier for the record... That would explain why it can't select a
record, and why it doesn't update the table, and why it won't move to a new
record. Hmmm... I'll get back to you. Thanks for helping me out!
--
Why are you asking me? I dont know what Im doing!

Jaybird

Doug, I actually used a default value for the textbox on the subform to
populate the [Load Number] from the mainform. Making this field visible on
the subform, I can see that it is working. However, I cannot select the
lineitem from the combo I created in the subform. So, what I thought was one
problem is actually another. I'm not good at working with recordset clones,
so I counted on the wizard to set up the combobox for me. I guess I must
have changed something that is preventing the recordset from being updated.
I'll try removing the combobox and re-creating it with the wizard and see
where that gets me.

You're making the problem much harder than it needs to be.

You do not need a default value for the load number. You do not need any code
to set the load number. You do not need a recordsetclone. You don't need
ANYTHING to populate the load number in the subform other than the master and
child link fields!

The way they work is to filter the subform to show only those detail records
corresponding to the main form's load number, *and* to automatically fill in
the value in the Master Link Field (the load number) into the Child Link Field
(the foreign key load number in the detail table). It's not actually necessary
to display either field, and if you're using an autonumber you usually
wouldn't.

It sounds like you're trying to manually do things that Access does for you
automatically!

John W. Vinson [MVP]

 

[Guest]

Nope. I still can't figure it out. I may know the maze pretty well, but
bring me in the wrong door and I WILL get lost... Any idea what I'm doing
wrong from my description?

 

[Douglas J. Steele]

To be honest, your explanations of what you see the problem as being confuse
me. I really don't understand what you're trying to do that's different from
the default behaviour of the typical form/subform setup. Sorry!

 

[Guest]

Well, I would like very much for this to behave like a normal subform.
Something I did must have made it all catywhompus. I should start from
scratch, using the wizard...

 

[John W. Vinson]

I have, as the underlying query for my
subform (sbfHeatTreat) a fully updatable query called qryHTDetails. With
it, I can fully populate all the fields with information about the load
lineitems by using the column property and my combobox [txtOrderEntry].

Now... wait a minute here.

It sounds like you're copying data from one table (the row source of
txtOrderEntry, an odd name for a combo box but OK) into another table.

Storing data redundantly is *NEVER* a good idea, and I fear that's just what
you're doing!

What Tables are involved here? What are the Recorsource properties of the main
and subform (post the SQL)? What's the Rowsource of the combo (ditto)?

John W. Vinson [MVP]

 

[Guest]

Doug,

Sorry I haven't gotten back to you until now. I got sick yesterday, so I
stayed home. Anyhow, if you're still monitoring this post, I'm still lost.
I don't know what I'm doing wrong. I've made similar forms many times in the
past, but this one has a different table structure behind it. Honestly,
though, I don't think that's the problem. Let me go through my set up again.
It will help me to think about what may be going wrong, anyhow.

I have a form called HT Load Info. It is designed to be a log of all items
going into the Heat Treat furnace. I figured then that all of the
information concerning the load as a whole would be based on a table called
(imaginatively) HT Load Info. This table uses Load Number as its primary key
and includes a bunch of other information about the load in general. The
form HT Load Info is based on this table only.

On this form, I have included a subform called sbfHeatTreat which, as you
might imagine, includes information about the items included in the load such
as part number, qty, job number, etc. this subform is based on the query
qryHTDetails which is made up of two tables. Table tblPartNumbers includes
static information specific to part numbers, such as material, size and
weight. It is joined in a one to many relationship with table
tblOrderDetails. This table includes information about our customer orders
that will vary from order to order such as qty, and price. They are joined
by the field PartID. Included in the table tblOrderDetails is a field called
Load Number, which is to act as a foreign key so that I can link the main
form to the subform.

Well and good so far. sbfHeatTreat is a continuous subform so that I can
add as many records as I need to the load going into the furnace. I use a
field called RecordID as my unique identifier for the line item. This comes
from the table tblOrderDetails so that I can use the information about these
parts that are already in the database. So, I use a combobox for the user to
select those RecordID's and include them in the load. This is where I get
into trouble...

My combobox uses qryHTDetails as its row source. The user selects the
appropriate Order Number with it's associated Part Numbers, qtys, and other
information so that he or she can be sure that they are selecting the correct
items. He or she verifies the correct qty by recording the actual count of
parts to the field HTQty.

So, I think I have all the elements... The important information from the
main form is Load Number. Load Number links all records from table
tblOrderDetails to the record in the main form that have the same Load
Number. I select the records in the subform by a combobox (created using the
wizard that looks for records in the query qryHTDetails). The unique
identifier for the record is RecordID, and the field linking tblHTDetails to
the table HT Load Info is Load Number.

You have said that when I make a selection in my subform, that the Load
Number currently being viewed in the main form is automatically assigned to
those records that I pick from my combobox. This is not what is happening.
The Load Number is not being recorded.

I would like the combobox to search for the record in the query
qryHTDetails, select the record, record the current Load Number, and allow
the HT Qty field to be edited. I've tried all kinds of combinations,
including using the combobox to search for records in qryHTDetails, look for
records in table tblOrderDetails, and so on. I can't seem to get anything to
work. I must have a great big blood clot somewhere in my brain because I've
done this sort of thing before without issue. Now I can't seem to figure out
what I'm doing. Can you see where I'm going wrong?
--
Why are you asking me? I dont know what Im doing!

Jaybird

Doug, I actually used a default value for the textbox on the subform to
populate the [Load Number] from the mainform. Making this field visible on
the subform, I can see that it is working. However, I cannot select the
lineitem from the combo I created in the subform. So, what I thought was one
problem is actually another. I'm not good at working with recordset clones,
so I counted on the wizard to set up the combobox for me. I guess I must
have changed something that is preventing the recordset from being updated.
I'll try removing the combobox and re-creating it with the wizard and see
where that gets me.

You're making the problem much harder than it needs to be.

You do not need a default value for the load number. You do not need any code
to set the load number. You do not need a recordsetclone. You don't need
ANYTHING to populate the load number in the subform other than the master and
child link fields!

The way they work is to filter the subform to show only those detail records
corresponding to the main form's load number, *and* to automatically fill in
the value in the Master Link Field (the load number) into the Child Link Field
(the foreign key load number in the detail table). It's not actually necessary
to display either field, and if you're using an autonumber you usually
wouldn't.

It sounds like you're trying to manually do things that Access does for you
automatically!

John W. Vinson [MVP]

 

[Douglas J. Steele]

Have you set up your LinkMasterFields/LinkChildFields to point to the
correct field(s) in the recordsets of the main form and subform
respectively?

However, I am unsure of one thing. When you select a record in the combo box
on the subform, it'll be associated with a specific Load Number, correct? If
that Load Number is not the Load Number for the active record on the main
form, you're going to have a problem.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Sorry I haven't gotten back to you until now. I got sick yesterday, so I
stayed home. Anyhow, if you're still monitoring this post, I'm still
lost.
I don't know what I'm doing wrong. I've made similar forms many times in
the
past, but this one has a different table structure behind it. Honestly,
though, I don't think that's the problem. Let me go through my set up
again.
It will help me to think about what may be going wrong, anyhow.

I have a form called HT Load Info. It is designed to be a log of all
items
going into the Heat Treat furnace. I figured then that all of the
information concerning the load as a whole would be based on a table
called
(imaginatively) HT Load Info. This table uses Load Number as its primary
key
and includes a bunch of other information about the load in general. The
form HT Load Info is based on this table only.

On this form, I have included a subform called sbfHeatTreat which, as you
might imagine, includes information about the items included in the load
such
as part number, qty, job number, etc. this subform is based on the query
qryHTDetails which is made up of two tables. Table tblPartNumbers
includes
static information specific to part numbers, such as material, size and
weight. It is joined in a one to many relationship with table
tblOrderDetails. This table includes information about our customer
orders
that will vary from order to order such as qty, and price. They are
joined
by the field PartID. Included in the table tblOrderDetails is a field
called
Load Number, which is to act as a foreign key so that I can link the main
form to the subform.

Well and good so far. sbfHeatTreat is a continuous subform so that I can
add as many records as I need to the load going into the furnace. I use a
field called RecordID as my unique identifier for the line item. This
comes
from the table tblOrderDetails so that I can use the information about
these
parts that are already in the database. So, I use a combobox for the user
to
select those RecordID's and include them in the load. This is where I get
into trouble...

My combobox uses qryHTDetails as its row source. The user selects the
appropriate Order Number with it's associated Part Numbers, qtys, and
other
information so that he or she can be sure that they are selecting the
correct
items. He or she verifies the correct qty by recording the actual count
of
parts to the field HTQty.

So, I think I have all the elements... The important information from the
main form is Load Number. Load Number links all records from table
tblOrderDetails to the record in the main form that have the same Load
Number. I select the records in the subform by a combobox (created using
the
wizard that looks for records in the query qryHTDetails). The unique
identifier for the record is RecordID, and the field linking tblHTDetails
to
the table HT Load Info is Load Number.

You have said that when I make a selection in my subform, that the Load
Number currently being viewed in the main form is automatically assigned
to
those records that I pick from my combobox. This is not what is
happening.
The Load Number is not being recorded.

I would like the combobox to search for the record in the query
qryHTDetails, select the record, record the current Load Number, and allow
the HT Qty field to be edited. I've tried all kinds of combinations,
including using the combobox to search for records in qryHTDetails, look
for
records in table tblOrderDetails, and so on. I can't seem to get anything
to
work. I must have a great big blood clot somewhere in my brain because
I've
done this sort of thing before without issue. Now I can't seem to figure
out
what I'm doing. Can you see where I'm going wrong?
--
Why are you asking me? I dont know what Im doing!

Jaybird

Doug, I actually used a default value for the textbox on the subform to
populate the [Load Number] from the mainform. Making this field visible
on
the subform, I can see that it is working. However, I cannot select the
lineitem from the combo I created in the subform. So, what I thought
was one
problem is actually another. I'm not good at working with recordset
clones,
so I counted on the wizard to set up the combobox for me. I guess I
must
have changed something that is preventing the recordset from being
updated.
I'll try removing the combobox and re-creating it with the wizard and
see
where that gets me.

You're making the problem much harder than it needs to be.

You do not need a default value for the load number. You do not need any
code
to set the load number. You do not need a recordsetclone. You don't need
ANYTHING to populate the load number in the subform other than the master
and
child link fields!

The way they work is to filter the subform to show only those detail
records
corresponding to the main form's load number, *and* to automatically fill
in
the value in the Master Link Field (the load number) into the Child Link
Field
(the foreign key load number in the detail table). It's not actually
necessary
to display either field, and if you're using an autonumber you usually
wouldn't.

It sounds like you're trying to manually do things that Access does for
you
automatically!

John W. Vinson [MVP]

 

[Guest]

I think it's evident that I thought I knew more than I do... I have since
learned a great deal from all of you on this subject and thank you very much
for trying to help me out.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Doug, I actually used a default value for the textbox on the subform to
populate the [Load Number] from the mainform. Making this field visible on
the subform, I can see that it is working. However, I cannot select the
lineitem from the combo I created in the subform. So, what I thought was one
problem is actually another. I'm not good at working with recordset clones,
so I counted on the wizard to set up the combobox for me. I guess I must
have changed something that is preventing the recordset from being updated.
I'll try removing the combobox and re-creating it with the wizard and see
where that gets me.

You're making the problem much harder than it needs to be.

You do not need a default value for the load number. You do not need any code
to set the load number. You do not need a recordsetclone. You don't need
ANYTHING to populate the load number in the subform other than the master and
child link fields!

The way they work is to filter the subform to show only those detail records
corresponding to the main form's load number, *and* to automatically fill in
the value in the Master Link Field (the load number) into the Child Link Field
(the foreign key load number in the detail table). It's not actually necessary
to display either field, and if you're using an autonumber you usually
wouldn't.

It sounds like you're trying to manually do things that Access does for you
automatically!

John W. Vinson [MVP]