Subform w/defaults from main form

[Guest]

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[strive4peace]

Hi Bob,

saving a record where all you have are defaultvalues is a problem... try
this:

code behind form:

'~~~~~~~~~~~~
Private Function SaveMe()
Me.ActiveControl = nz(Me.ActiveControl)
If Me.Dirty Then Me.Dirty = False
DoCmd.RunCommand acCmdRecordsGoToNew
End Function
'~~~~~~~~~~~~

on the Double-click event of each control -->
=SaveMe()

so, if your users want to keep the record as is, they can just
double-click on any control

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Crystal, thank you for the response. I tried your suggestion but it doesn't
solve my problem. After re-reading my question I don't think I have been very
clear. What I am trying to do is: When I finish entering data in my PO header
and tab to the subform where I enter purchase order line item data I can't
get default data to appear in two fields in the subform. The data has been
entered in the PO header form. When I enter data in the first field of the
subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the PO header.
Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data to appear is
to click the esc key twice. The two fields I am trying to update are a
purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a new line
item in my subform.

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[strive4peace]

Hi Bob,

"The data has been entered in the PO header form. When I enter data in
the first field of the subform a second line appears below the line
where I am entering data on the subform."

perhaps you are approaching this from the wrong angle... here is an
analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Crystal, thank you for the response. I tried your suggestion but it doesn't
solve my problem. After re-reading my question I don't think I have been very
clear. What I am trying to do is: When I finish entering data in my PO header
and tab to the subform where I enter purchase order line item data I can't
get default data to appear in two fields in the subform. The data has been
entered in the PO header form. When I enter data in the first field of the
subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the PO header.
Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data to appear is
to click the esc key twice. The two fields I am trying to update are a
purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a new line
item in my subform.

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[Guest]

Thanks for the additional help. As of now I can get the defaults to appear on
the subform. The default data resides in a table called "poheader" and
appears in the subform after I enter a number in the first field of the first
new record in the subform and click escape twice. After adding the first
record, the default data appears ok. When I first get focus on the first
field of the subform I can't get the defaults to appear until I enter data in
the first field and click escape twice. If I could program the action of
clicking escape twice when I get focus on the first field of the first record
in the subform everything would be ok. Is there a way to do this?

Hi Bob,

"The data has been entered in the PO header form. When I enter data in
the first field of the subform a second line appears below the line
where I am entering data on the subform."

perhaps you are approaching this from the wrong angle... here is an
analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Crystal, thank you for the response. I tried your suggestion but it doesn't
solve my problem. After re-reading my question I don't think I have been very
clear. What I am trying to do is: When I finish entering data in my PO header
and tab to the subform where I enter purchase order line item data I can't
get default data to appear in two fields in the subform. The data has been
entered in the PO header form. When I enter data in the first field of the
subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the PO header.
Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data to appear is
to click the esc key twice. The two fields I am trying to update are a
purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a new line
item in my subform.

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[strive4peace]

Hi Bob,

you said, 'The default data resides in a table called "poheader"'

How are you setting your controls to the default values?

Are you storing the same information in more than one place? If you
have info in the table for the subform that is also in your POHeader,
you should just be storing a related ID and let Access show the other
values from your poheader table in calculated controls

"Is there a way to do this?"

yes, there is, using SendKeys, but that is not a good idea... and rather
than do this, let us explore what you really should be doing

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for the additional help. As of now I can get the defaults to appear on
the subform. The default data resides in a table called "poheader" and
appears in the subform after I enter a number in the first field of the first
new record in the subform and click escape twice. After adding the first
record, the default data appears ok. When I first get focus on the first
field of the subform I can't get the defaults to appear until I enter data in
the first field and click escape twice. If I could program the action of
clicking escape twice when I get focus on the first field of the first record
in the subform everything would be ok. Is there a way to do this?

Hi Bob,

"The data has been entered in the PO header form. When I enter data in
the first field of the subform a second line appears below the line
where I am entering data on the subform."

perhaps you are approaching this from the wrong angle... here is an
analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Crystal, thank you for the response. I tried your suggestion but it doesn't
solve my problem. After re-reading my question I don't think I have been very
clear. What I am trying to do is: When I finish entering data in my PO header
and tab to the subform where I enter purchase order line item data I can't
get default data to appear in two fields in the subform. The data has been
entered in the PO header form. When I enter data in the first field of the
subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the PO header.
Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data to appear is
to click the esc key twice. The two fields I am trying to update are a
purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a new line
item in my subform.

:

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[Guest]

I'm sorry to be so thick headed about this. I'm new to Access. I very much
appreciate the help I have received from this discussion group. Let me
explain how my data is set up and what I'm trying to do. I have a table
called poheader. One of the fields in this table is poduedate. When a new
purchase order is entered one of the fields entered is the expected date
purchased material will be delivered. That data is contained in poduedate.
There is also a table called polinefile. Polinefile contains data for each
item that is being purchased in a specific purchase order. So, there will be
a single entry for each purchase order in the poheader table with the
purchase order number as the key. In the polinefile there exist multiple line
items per purchase order. The polinefile key is made up of the purchase order
number and a line item. In the end there would be a purchase order with
multiple line items, each line item listing specific material that is being
purchased as well as a specific delivery due date. To make data entry easier
I decided that when line items were entered in the subform (polinefile)
instead of having to enter the due date for each line item I could use the
due date from the poheader as the default. In most cases this would be the
correct date. When a different due date would be required, I could just
overide the default date by entering the date needed. The date would exist in
the polinefile as polduedate. All this seems to be working ok.
My only problem is that when I enter a new purchase order, finish entering
data in the poheader portion of the form and tab into the polinefile subform
and into the first field of the first line item of the subform I can't get
that line to display the default due date, which is poduedate from the
poheader. After I enter the first line item subsequent line items are ok.
They display the default due date. I don't understand how to control the
first line of data entry. I've tried to use setfocus and requery but I don't
have enough experience to get things to work as I want them to.

Hi Bob,

you said, 'The default data resides in a table called "poheader"'

How are you setting your controls to the default values?

Are you storing the same information in more than one place? If you
have info in the table for the subform that is also in your POHeader,
you should just be storing a related ID and let Access show the other
values from your poheader table in calculated controls

"Is there a way to do this?"

yes, there is, using SendKeys, but that is not a good idea... and rather
than do this, let us explore what you really should be doing

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for the additional help. As of now I can get the defaults to appear on
the subform. The default data resides in a table called "poheader" and
appears in the subform after I enter a number in the first field of the first
new record in the subform and click escape twice. After adding the first
record, the default data appears ok. When I first get focus on the first
field of the subform I can't get the defaults to appear until I enter data in
the first field and click escape twice. If I could program the action of
clicking escape twice when I get focus on the first field of the first record
in the subform everything would be ok. Is there a way to do this?

Hi Bob,

"The data has been entered in the PO header form. When I enter data in
the first field of the subform a second line appears below the line
where I am entering data on the subform."

perhaps you are approaching this from the wrong angle... here is an
analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Bobk wrote:
Crystal, thank you for the response. I tried your suggestion but it doesn't
solve my problem. After re-reading my question I don't think I have been very
clear. What I am trying to do is: When I finish entering data in my PO header
and tab to the subform where I enter purchase order line item data I can't
get default data to appear in two fields in the subform. The data has been
entered in the PO header form. When I enter data in the first field of the
subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the PO header.
Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data to appear is
to click the esc key twice. The two fields I am trying to update are a
purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a new line
item in my subform.

:

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[tina]

you posted a good and pretty thorough description here - makes things a lot
easier. i'm guessing that you set the DefaultValue property of the control
in the subform to refer to the control in the main form. if that's what you
did, then i think the problem is caused by the fact that the subform
actually opens and loads *before* the main form - at which time there is no
value to refer to, for the first subform record.

instead of setting the DefaultValue property of the subform control, you
could add a simple line of code to the subform's Form_BeforeInsert event
procedure, to set the value as each subform record (1st, 2nd, or 100th,
whatever) is created, as

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!PolDueDate = Me.Parent!PolDueDate

End Sub

"Me!PolDueDate" refers to the due date field in the subform.
"Me.Parent!PolDueDate" refers to the due date field in the main form. make
sure you add the procedure to the *subform's* BeforeInsert event, *not* to
the main form.

if you're not familiar with creating event procedures in Access, go to
http://home.att.net/~california.db/instructions.html and click on the
CreateEventProcedure link for illustrated step-by-step instructions.

hth

I'm sorry to be so thick headed about this. I'm new to Access. I very much
appreciate the help I have received from this discussion group. Let me
explain how my data is set up and what I'm trying to do. I have a table
called poheader. One of the fields in this table is poduedate. When a new
purchase order is entered one of the fields entered is the expected date
purchased material will be delivered. That data is contained in poduedate.
There is also a table called polinefile. Polinefile contains data for each
item that is being purchased in a specific purchase order. So, there will be
a single entry for each purchase order in the poheader table with the
purchase order number as the key. In the polinefile there exist multiple line
items per purchase order. The polinefile key is made up of the purchase order
number and a line item. In the end there would be a purchase order with
multiple line items, each line item listing specific material that is being
purchased as well as a specific delivery due date. To make data entry easier
I decided that when line items were entered in the subform (polinefile)
instead of having to enter the due date for each line item I could use the
due date from the poheader as the default. In most cases this would be the
correct date. When a different due date would be required, I could just
overide the default date by entering the date needed. The date would exist in
the polinefile as polduedate. All this seems to be working ok.
My only problem is that when I enter a new purchase order, finish entering
data in the poheader portion of the form and tab into the polinefile subform
and into the first field of the first line item of the subform I can't get
that line to display the default due date, which is poduedate from the
poheader. After I enter the first line item subsequent line items are ok.
They display the default due date. I don't understand how to control the
first line of data entry. I've tried to use setfocus and requery but I don't
have enough experience to get things to work as I want them to.

Hi Bob,

you said, 'The default data resides in a table called "poheader"'

How are you setting your controls to the default values?

Are you storing the same information in more than one place? If you
have info in the table for the subform that is also in your POHeader,
you should just be storing a related ID and let Access show the other
values from your poheader table in calculated controls

"Is there a way to do this?"

yes, there is, using SendKeys, but that is not a good idea... and rather
than do this, let us explore what you really should be doing

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for the additional help. As of now I can get the defaults to appear on
the subform. The default data resides in a table called "poheader" and
appears in the subform after I enter a number in the first field of the first
new record in the subform and click escape twice. After adding the first
record, the default data appears ok. When I first get focus on the first
field of the subform I can't get the defaults to appear until I enter data in
the first field and click escape twice. If I could program the action of
clicking escape twice when I get focus on the first field of the first record
in the subform everything would be ok. Is there a way to do this?

:

Hi Bob,

"The data has been entered in the PO header form. When I enter data in
the first field of the subform a second line appears below the line
where I am entering data on the subform."

perhaps you are approaching this from the wrong angle... here is an
analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this.

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Bobk wrote:
Crystal, thank you for the response. I tried your suggestion but it doesn't
solve my problem. After re-reading my question I don't think I have been very
clear. What I am trying to do is: When I finish entering data in my PO header
and tab to the subform where I enter purchase order line item data I can't
get default data to appear in two fields in the subform. The data has been
entered in the PO header form. When I enter data in the first field of the
subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the PO header.
Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data to appear is
to click the esc key twice. The two fields I am trying to update are a
purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a new line
item in my subform.

:

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[Guest]

tina-
Thank you. Your code worked perfectly. I have been searching for a solution
for months and here it is. I appreciate your response and help.

you posted a good and pretty thorough description here - makes things a lot
easier. i'm guessing that you set the DefaultValue property of the control
in the subform to refer to the control in the main form. if that's what you
did, then i think the problem is caused by the fact that the subform
actually opens and loads *before* the main form - at which time there is no
value to refer to, for the first subform record.

instead of setting the DefaultValue property of the subform control, you
could add a simple line of code to the subform's Form_BeforeInsert event
procedure, to set the value as each subform record (1st, 2nd, or 100th,
whatever) is created, as

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!PolDueDate = Me.Parent!PolDueDate

End Sub

"Me!PolDueDate" refers to the due date field in the subform.
"Me.Parent!PolDueDate" refers to the due date field in the main form. make
sure you add the procedure to the *subform's* BeforeInsert event, *not* to
the main form.

if you're not familiar with creating event procedures in Access, go to
http://home.att.net/~california.db/instructions.html and click on the
CreateEventProcedure link for illustrated step-by-step instructions.

hth

I'm sorry to be so thick headed about this. I'm new to Access. I very much
appreciate the help I have received from this discussion group. Let me
explain how my data is set up and what I'm trying to do. I have a table
called poheader. One of the fields in this table is poduedate. When a new
purchase order is entered one of the fields entered is the expected date
purchased material will be delivered. That data is contained in poduedate.
There is also a table called polinefile. Polinefile contains data for each
item that is being purchased in a specific purchase order. So, there will be
a single entry for each purchase order in the poheader table with the
purchase order number as the key. In the polinefile there exist multiple line
items per purchase order. The polinefile key is made up of the purchase order
number and a line item. In the end there would be a purchase order with
multiple line items, each line item listing specific material that is being
purchased as well as a specific delivery due date. To make data entry easier
I decided that when line items were entered in the subform (polinefile)
instead of having to enter the due date for each line item I could use the
due date from the poheader as the default. In most cases this would be the
correct date. When a different due date would be required, I could just
overide the default date by entering the date needed. The date would exist in
the polinefile as polduedate. All this seems to be working ok.
My only problem is that when I enter a new purchase order, finish entering
data in the poheader portion of the form and tab into the polinefile subform
and into the first field of the first line item of the subform I can't get
that line to display the default due date, which is poduedate from the
poheader. After I enter the first line item subsequent line items are ok.
They display the default due date. I don't understand how to control the
first line of data entry. I've tried to use setfocus and requery but I don't
have enough experience to get things to work as I want them to.

Hi Bob,

you said, 'The default data resides in a table called "poheader"'

How are you setting your controls to the default values?

Are you storing the same information in more than one place? If you
have info in the table for the subform that is also in your POHeader,
you should just be storing a related ID and let Access show the other
values from your poheader table in calculated controls

"Is there a way to do this?"

yes, there is, using SendKeys, but that is not a good idea... and rather
than do this, let us explore what you really should be doing

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Bobk wrote:
Thanks for the additional help. As of now I can get the defaults to appear on
the subform. The default data resides in a table called "poheader" and
appears in the subform after I enter a number in the first field of the first
new record in the subform and click escape twice. After adding the first
record, the default data appears ok. When I first get focus on the first
field of the subform I can't get the defaults to appear until I enter data in
the first field and click escape twice. If I could program the action of
clicking escape twice when I get focus on the first field of the first record
in the subform everything would be ok. Is there a way to do this?

:

Hi Bob,

"The data has been entered in the PO header form. When I enter data in
the first field of the subform a second line appears below the line
where I am entering data on the subform."

perhaps you are approaching this from the wrong angle... here is an
analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this.

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Bobk wrote:
Crystal, thank you for the response. I tried your suggestion but it doesn't
solve my problem. After re-reading my question I don't think I have been very
clear. What I am trying to do is: When I finish entering data in my PO header
and tab to the subform where I enter purchase order line item data I can't
get default data to appear in two fields in the subform. The data has been
entered in the PO header form. When I enter data in the first field of the
subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the PO header.
Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data to appear is
to click the esc key twice. The two fields I am trying to update are a
purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a new line
item in my subform.

:

I have been working on a purchase order entry system for several months and
have got things working pretty well except for a a problem I have with a
subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items common to the PO.
The poline table contains data for each line item on the PO. The two tables
are used in a form called poentry. The subform is poline. In the poheader I
enter data such as vendor information and also enter a po due date and a
material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants to override the
defaults he can do so. When entering data for the poheader we tab through
each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line where data is
being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update and I can
re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data entry is set as
"No". The defaults are set in the default value property. I have a "New"
button on the form that I use to prepare to enter a new record. The code is:
DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things work ok. It is
just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found any reference
to a problem like this anywhere.

 

[tina]

you're welcome

tina-
Thank you. Your code worked perfectly. I have been searching for a solution
for months and here it is. I appreciate your response and help.

you posted a good and pretty thorough description here - makes things a lot
easier. i'm guessing that you set the DefaultValue property of the control
in the subform to refer to the control in the main form. if that's what you
did, then i think the problem is caused by the fact that the subform
actually opens and loads *before* the main form - at which time there is no
value to refer to, for the first subform record.

instead of setting the DefaultValue property of the subform control, you
could add a simple line of code to the subform's Form_BeforeInsert event
procedure, to set the value as each subform record (1st, 2nd, or 100th,
whatever) is created, as

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!PolDueDate = Me.Parent!PolDueDate

End Sub

"Me!PolDueDate" refers to the due date field in the subform.
"Me.Parent!PolDueDate" refers to the due date field in the main form. make
sure you add the procedure to the *subform's* BeforeInsert event, *not* to
the main form.

if you're not familiar with creating event procedures in Access, go to
http://home.att.net/~california.db/instructions.html and click on the
CreateEventProcedure link for illustrated step-by-step instructions.

hth

I'm sorry to be so thick headed about this. I'm new to Access. I very much
appreciate the help I have received from this discussion group. Let me
explain how my data is set up and what I'm trying to do. I have a table
called poheader. One of the fields in this table is poduedate. When a new
purchase order is entered one of the fields entered is the expected date
purchased material will be delivered. That data is contained in poduedate.
There is also a table called polinefile. Polinefile contains data for each
item that is being purchased in a specific purchase order. So, there

will
be

a single entry for each purchase order in the poheader table with the
purchase order number as the key. In the polinefile there exist

multiple
line

items per purchase order. The polinefile key is made up of the

purchase
order

number and a line item. In the end there would be a purchase order with
multiple line items, each line item listing specific material that is being
purchased as well as a specific delivery due date. To make data entry easier
I decided that when line items were entered in the subform (polinefile)
instead of having to enter the due date for each line item I could use the
due date from the poheader as the default. In most cases this would be the
correct date. When a different due date would be required, I could just
overide the default date by entering the date needed. The date would

exist
in

the polinefile as polduedate. All this seems to be working ok.
My only problem is that when I enter a new purchase order, finish entering
data in the poheader portion of the form and tab into the polinefile subform
and into the first field of the first line item of the subform I can't get
that line to display the default due date, which is poduedate from the
poheader. After I enter the first line item subsequent line items are ok.
They display the default due date. I don't understand how to control the
first line of data entry. I've tried to use setfocus and requery but

I
don't

have enough experience to get things to work as I want them to.

:

Hi Bob,

you said, 'The default data resides in a table called "poheader"'

How are you setting your controls to the default values?

Are you storing the same information in more than one place? If you
have info in the table for the subform that is also in your POHeader,
you should just be storing a related ID and let Access show the other
values from your poheader table in calculated controls

"Is there a way to do this?"

yes, there is, using SendKeys, but that is not a good idea... and rather
than do this, let us explore what you really should be doing

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Bobk wrote:
Thanks for the additional help. As of now I can get the defaults

to
appear on

the subform. The default data resides in a table called "poheader" and
appears in the subform after I enter a number in the first field

of
the first

new record in the subform and click escape twice. After adding the first
record, the default data appears ok. When I first get focus on the first
field of the subform I can't get the defaults to appear until I

enter
data in

the first field and click escape twice. If I could program the

action
of

clicking escape twice when I get focus on the first field of the

first
record

in the subform everything would be ok. Is there a way to do this?

:

Hi Bob,

"The data has been entered in the PO header form. When I enter

data
in

the first field of the subform a second line appears below the line
where I am entering data on the subform."

perhaps you are approaching this from the wrong angle... here is an
analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this.

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will

be
hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you

names
from

another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Bobk wrote:
Crystal, thank you for the response. I tried your suggestion but

it
doesn't

solve my problem. After re-reading my question I don't think I

have
been very

clear. What I am trying to do is: When I finish entering data in

my
PO header

and tab to the subform where I enter purchase order line item

data I
can't

get default data to appear in two fields in the subform. The

data
has been

entered in the PO header form. When I enter data in the first

field
of the

subform a second line appears below the line where I am entering data on the
subform. The new line contains the correct default data from the

PO
header.

Unfortunatly, the first line, where I am entering data, does not contain the
default data. The only way I have found to get the default data

to
appear is

to click the esc key twice. The two fields I am trying to update

are
a

purchase order due date and a material description. The data is correct - it
just isn't appearing in the right place the first time I enter a

new
line

item in my subform.

:

I have been working on a purchase order entry system for

several
months and

have got things working pretty well except for a a problem I

have
with a

subform. I have a purchase order entry routine that has two tables -
poheader and poline. The poheader table contains the items

common
to the PO.

The poline table contains data for each line item on the PO.

The
two tables

are used in a form called poentry. The subform is poline. In

the
poheader I

enter data such as vendor information and also enter a po due

date
and a

material description. These two fields are used as defaults for each line
item and are contained in the poline table. If the user wants

to
override the

defaults he can do so. When entering data for the poheader we

tab
through

each field and finally tab to the poline subfrom where tabs are setup to go
first to a line item number. The user then enters the line item number,
usually 1 for the first line item. When the number is entered a line for a
second record opens below the line that is being entered. It contains the
default data of material description and due date. The line

where
data is

being entered does not display the default data. If I enter a number in the
first field and click escape twice the default fields update

and I
can

re-enter the line item number in the first (active record) and continue,
othewise the default fields stay blank. The subform is set as continuous with
filters, edits, deletions and additions set as "Yes". Data

entry is
set as

"No". The defaults are set in the default value property. I

have a
"New"

button on the form that I use to prepare to enter a new record.

The
code is:

DoCmd.GoToRecord , , acNewRec
Me.[poorddate].SetFocus
I use this same form to do updates and additions and things

work
ok. It is

just that first line item entry on a new PO that is a problem.

Can anyone point me in the right direction. I have not found

any
reference

to a problem like this anywhere.