ACC2000 Copy chosen date to multiple tables--Coding...or query iss

B

BrentDA

I have a combo box drop-down list in a form, with custom date ranges
(manually keyed in to a table), e.g., Jan 1-3, Jan 4-10, Jan 11-18.

When the user selects a date range (and enters some numeric data in fields)
on the form, the user then clicks one of 3 buttons to go to a selection of
more forms to key in more numeric data.

What I'm looking for is to have the selected date range copied to a field in
all the subsequent forms/tables as a new record (because all the forms will
have numeric data entered each time the database is used.

I need the selected date range (from the initial form) tied to each new
record of numeric data entered. I'm doing this to eliminate the added work
of having to select the date range on each form, and also to avoid the
possibility of user input error.

I have it set up now so there is a 'hidden' date range field on each form
that the users don't need to see, but I can't get the date range to 'copy' to
all the forms.

Would I use coding, or set up a query to update the date range field in all
the forms/tables?

Thanks in advance for your help !

Brent
 
T

tkelley via AccessMonster.com

How about leaving the initial form open while the others pop up in front of
it. Then you can set the default value of the date fields in the new form to
the value of the initial form by using:

=Forms!frmInitialForm!Date1.value

Would that work for you?
 
B

BrentDA

Hi and thanks for your help !

If I understood you correctly, here's the code I put in the Record Source of
the 2nd form (called frmCVGMWLMtrxInpt):

[Forms]![DPU DPM INPUT FORM]![DPUDPMMthDte].[value]

'DPU DPM INPUT FORM' is the name of the first form. 'DPUDPMMthDte' is the
name of the date field on the first form.

Now, when I open the 2nd form (while leaving the 1st form open) I get the
following error message: 'Run-time error 2105: You can't go to the
specified record.'

I do have code in 'On Open' in each of my subsequent forms:
'DoCmd.GoToRecord , , acNewRec' to tell the form to go to a new, empty record
when opened.

I tried taking that code out of the form, and now when the form opens, it's
a 'blank slate'. There's nothing on it...just a gray area with no controls,
text or anything.

I'm not sure how to proceed at this point.

Again, thanks for your help.

Brent
 
T

tkelley via AccessMonster.com

Not the recordsource of the subform, the default value of the target field,
using the properties.

In the properties box of the target field, put this in default:

=[Forms]![DPU DPM INPUT FORM]![DPUDPMMthDte].[value]
Hi and thanks for your help !

If I understood you correctly, here's the code I put in the Record Source of
the 2nd form (called frmCVGMWLMtrxInpt):

[Forms]![DPU DPM INPUT FORM]![DPUDPMMthDte].[value]

'DPU DPM INPUT FORM' is the name of the first form. 'DPUDPMMthDte' is the
name of the date field on the first form.

Now, when I open the 2nd form (while leaving the 1st form open) I get the
following error message: 'Run-time error 2105: You can't go to the
specified record.'

I do have code in 'On Open' in each of my subsequent forms:
'DoCmd.GoToRecord , , acNewRec' to tell the form to go to a new, empty record
when opened.

I tried taking that code out of the form, and now when the form opens, it's
a 'blank slate'. There's nothing on it...just a gray area with no controls,
text or anything.

I'm not sure how to proceed at this point.

Again, thanks for your help.

Brent
How about leaving the initial form open while the others pop up in front of
it. Then you can set the default value of the date fields in the new form to
[quoted text clipped - 30 lines]
 
B

BrentDA

YES ! IT WORKED !!

Thanks much for your help !!

By the way, know of any good reference books for VB coding and/or queries?
I'm looking for something with step-by-step instructions and
pictures/diagrams. Something like a 'For Dummies' book. I feel comfortable
with the other areas of Access. It's only the coding/queries I have trouble
with.

Just to give you an idea, I'm using Access 2000 at work, currently Access
2007 at home (upgraded from 2003). I also have the book "Microsoft Access
2003 Inside Out", by John Viescas. Publisher: Microsoft Press.

Thanks !

Brent

tkelley via AccessMonster.com said:
Not the recordsource of the subform, the default value of the target field,
using the properties.

In the properties box of the target field, put this in default:

=[Forms]![DPU DPM INPUT FORM]![DPUDPMMthDte].[value]
Hi and thanks for your help !

If I understood you correctly, here's the code I put in the Record Source of
the 2nd form (called frmCVGMWLMtrxInpt):

[Forms]![DPU DPM INPUT FORM]![DPUDPMMthDte].[value]

'DPU DPM INPUT FORM' is the name of the first form. 'DPUDPMMthDte' is the
name of the date field on the first form.

Now, when I open the 2nd form (while leaving the 1st form open) I get the
following error message: 'Run-time error 2105: You can't go to the
specified record.'

I do have code in 'On Open' in each of my subsequent forms:
'DoCmd.GoToRecord , , acNewRec' to tell the form to go to a new, empty record
when opened.

I tried taking that code out of the form, and now when the form opens, it's
a 'blank slate'. There's nothing on it...just a gray area with no controls,
text or anything.

I'm not sure how to proceed at this point.

Again, thanks for your help.

Brent
How about leaving the initial form open while the others pop up in front of
it. Then you can set the default value of the date fields in the new form to
[quoted text clipped - 30 lines]
 
T

tkelley via AccessMonster.com

I'm glad I could help. As far as the books go, everything I'd want to
suggest would be more advanced. In the meantime, I'd just look around on
Amazon and read the user's comments.

http://www.amazon.com/s/ref=nb_ss_b...-keywords=access+2003+vba&sprefix=access+2003


Reading up on the generalities of how SQL works, and table design and
normalization is always a good thing to do too. When querying data, one of
the biggest favors you can do for the developer in you is to build databases
that are designed well, with proper realtionships, referential integrity, etc.


Good luck.
YES ! IT WORKED !!

Thanks much for your help !!

By the way, know of any good reference books for VB coding and/or queries?
I'm looking for something with step-by-step instructions and
pictures/diagrams. Something like a 'For Dummies' book. I feel comfortable
with the other areas of Access. It's only the coding/queries I have trouble
with.

Just to give you an idea, I'm using Access 2000 at work, currently Access
2007 at home (upgraded from 2003). I also have the book "Microsoft Access
2003 Inside Out", by John Viescas. Publisher: Microsoft Press.

Thanks !

Brent
Not the recordsource of the subform, the default value of the target field,
using the properties.
[quoted text clipped - 36 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top