Form Date Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have two questions.

1 - Scenario, I'm entering a new record in database using a form. After
entering the data, I want to click a button I created on the form and print a
letter containing info in the current record before going to the next record.
For example, I want the letter to contain the current active form information
such as the customer name, address, etc. Will this also work for a record I
queried from the database and is present in a form on the screen?

2 - I want to be able to click a button on my main form that will open/print
a form of the current month's sales but without having to put 11/1/2005 &
11/30/2005 in a pop-up box prompted by the query.

Can anyone help me with either or both of these?

Thanks in advance.
KP
 
Hi, I have two questions.

1 - Scenario, I'm entering a new record in database using a form. After
entering the data, I want to click a button I created on the form and print a
letter containing info in the current record before going to the next record.
For example, I want the letter to contain the current active form information
such as the customer name, address, etc. Will this also work for a record I
queried from the database and is present in a form on the screen?
Click to expand...

Your table should have a unique prime key field.

If so, code the command button's Click event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If [RecordID] is Text Datatype, then use:

"[RecordID] = '" & [RecordID] & "'"

as the Where clause.
Change the field name to whatever the actual field name is that you
are using.

See Access Help files for:
Where Clause + Restrict data to a subset of records'
2 - I want to be able to click a button on my main form that will open/print
a form of the current month's sales but without having to put 11/1/2005 &
11/30/2005 in a pop-up box prompted by the query.

Can anyone help me with either or both of these?

Thanks in advance.
KP
Click to expand...

Print a form? Nope. Don't do that.
Print a Report? Yes.

I'll assume you know how to create the report and open the date form
in dialog to enter the dates in. If you have a problem crateing a
report, post back.

Several ways to print a report for the current month without entering
the first and last day. Here is one.

Assuming the report will also be used to select different dates and
you already have a date form set up, set the default value of the
Start date control on the form to:
DateSerial(Year(Date()),Month(Date()),1)

Set the DefaultValue for the End date control to:
DateSerial(Year(Date()),Month(Date())+1,0)

The controls will always default to the first and last days of the
current month. Just click the command button on the form to proceed
with the report.
After the 1st of the following month you can simply overwrite the
default to get a previous month's results.
 
Hi Fred, thanks for the reply.
I've looked hi & lo for the start date and end date control but can't find
it. Is this a property on the date field on the form or do I need to create a
macro/module to get this accomplished, or something else I need to do/look
for?

Thanks in advance.

fredg said:
Hi, I have two questions.

1 - Scenario, I'm entering a new record in database using a form. After
entering the data, I want to click a button I created on the form and print a
letter containing info in the current record before going to the next record.
For example, I want the letter to contain the current active form information
such as the customer name, address, etc. Will this also work for a record I
queried from the database and is present in a form on the screen?
Click to expand...

Your table should have a unique prime key field.

If so, code the command button's Click event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If [RecordID] is Text Datatype, then use:

"[RecordID] = '" & [RecordID] & "'"

as the Where clause.
Change the field name to whatever the actual field name is that you
are using.

See Access Help files for:
Where Clause + Restrict data to a subset of records'
2 - I want to be able to click a button on my main form that will open/print
a form of the current month's sales but without having to put 11/1/2005 &
11/30/2005 in a pop-up box prompted by the query.

Can anyone help me with either or both of these?

Thanks in advance.
KP
Click to expand...

Print a form? Nope. Don't do that.
Print a Report? Yes.

I'll assume you know how to create the report and open the date form
in dialog to enter the dates in. If you have a problem crateing a
report, post back.

Several ways to print a report for the current month without entering
the first and last day. Here is one.

Assuming the report will also be used to select different dates and
you already have a date form set up, set the default value of the
Start date control on the form to:
DateSerial(Year(Date()),Month(Date()),1)

Set the DefaultValue for the End date control to:
DateSerial(Year(Date()),Month(Date())+1,0)

The controls will always default to the first and last days of the
current month. Just click the command button on the form to proceed
with the report.
After the 1st of the following month you can simply overwrite the
default to get a previous month's results.
Click to expand...
 
Hi Fred, thanks for the reply.
I've looked hi & lo for the start date and end date control but can't find
it. Is this a property on the date field on the form or do I need to create a
macro/module to get this accomplished, or something else I need to do/look
for?

Thanks in advance.

fredg said:
Hi, I have two questions.

1 - Scenario, I'm entering a new record in database using a form. After
entering the data, I want to click a button I created on the form and print a
letter containing info in the current record before going to the next record.
For example, I want the letter to contain the current active form information
such as the customer name, address, etc. Will this also work for a record I
queried from the database and is present in a form on the screen?
Click to expand...

Your table should have a unique prime key field.

If so, code the command button's Click event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If [RecordID] is Text Datatype, then use:

"[RecordID] = '" & [RecordID] & "'"

as the Where clause.
Change the field name to whatever the actual field name is that you
are using.

See Access Help files for:
Where Clause + Restrict data to a subset of records'
2 - I want to be able to click a button on my main form that will open/print
a form of the current month's sales but without having to put 11/1/2005 &
11/30/2005 in a pop-up box prompted by the query.

Can anyone help me with either or both of these?

Thanks in advance.
KP
Click to expand...

Print a form? Nope. Don't do that.
Print a Report? Yes.

I'll assume you know how to create the report and open the date form
in dialog to enter the dates in. If you have a problem crateing a
report, post back.

Several ways to print a report for the current month without entering
the first and last day. Here is one.

Assuming the report will also be used to select different dates and
you already have a date form set up, set the default value of the
Start date control on the form to:
DateSerial(Year(Date()),Month(Date()),1)

Set the DefaultValue for the End date control to:
DateSerial(Year(Date()),Month(Date())+1,0)

The controls will always default to the first and last days of the
current month. Just click the command button on the form to proceed
with the report.
After the 1st of the following month you can simply overwrite the
default to get a previous month's results.
Click to expand...
Click to expand...

If you don't already have a form to enter the date range in you have
to create one.
If you wish to enter a date range and print a report of records within
that range, here is how.

First create a query that will return ALL of the records and fields
wanted in the report.
Make this query the record source of your report.

Next....
Create an unbound form.

Add 2 unbound text controls.
Set their format to a valid date format.

*** In these controls is where you would set the DateSerial() Default
Values mentioned in my previous reply. ***

Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the entry of the
starting and ending dates wanted. If you wish to use the default
dates, just click the command button. If you want a different range of
dates, just enter them in the boxes.
Click the command button and then report will run.
When the report closes, it will close the form.
 
Back
Top