forms: simple and complex question

[someguy]

Hi,

I'm trying to accomplish the following on a form on which i have
removed navigational buttons (I apologize in advance if this is too many
questions);

The two easy ones are....
1) When the user opens the forms I would like it to open to a blank
form so they can create a new record (I don't want fields populated with
the first record from the table). The user should also have the option of
searching for a particular record.

2) Would like to do some data testing done on the data the user
entered in the forms before it updates the table. Basically i would like to
concatenate a certain amount of zeros to what they entered using a loop.
Where do i place this code?

The more difficult one...
3) Would like to add a button to a form that would create and save a
CSV file by joining certain tables in the DB. The user should be prompted
to where he/she wants the file saved.

TIA

[Ed Robichaud]

1. Set the Data Entry (and Allow Additions) property of the form to "Yes"

2. Not sure what you mean by "data testing". You can use the Format
property of your controls to pad leading or trailing zeros (if that's what
you want).

3. A query of your two tables will create a recordset of the data in both
tables. Make that query a "Make Table" one and follow the prompts to select
a file type and location.

-Ed

"someguy" <in*********@ya hoo.com> wrote in message
news:Xn******** *************** **********@216. 196.97.142...

Hi,

I'm trying to accomplish the following on a form on which i have
removed navigational buttons (I apologize in advance if this is too many
questions);

The two easy ones are....
1) When the user opens the forms I would like it to open to a blank
form so they can create a new record (I don't want fields populated with
the first record from the table). The user should also have the option of
searching for a particular record.

2) Would like to do some data testing done on the data the user
entered in the forms before it updates the table. Basically i would like
to
concatenate a certain amount of zeros to what they entered using a loop.
Where do i place this code?

The more difficult one...
3) Would like to add a button to a form that would create and save a
CSV file by joining certain tables in the DB. The user should be prompted
to where he/she wants the file saved.

TIA

[Jean]

Hi someguy,

I'm not too sure about Question 1...I think you can set the Form's
..CurrentRecord property to the last record, in the Form's OnOpen event
handler.

2) If you can write the code, you can either make it a public function
within a standard code module (i.e. in the VBE Project Explorer, under
'Modules') OR you can make it a private function within your form's
class module (i.e. in VBE Project Explorer, under 'Microsoft Access
class object')

3) I have done this before and tackled it in two ways:
Firstly, you can use the TransferSpreads heet method in your button's
OnClick code, to transfer your data to a spreadsheet(whi ch can be saved
as a .csv file). See the Help file in Access VBA for using this method.
Then, I suggest you read the following link, it has some useful tips on
automating Excel from another application - in your case saving the
file and prompting the user where to save.

Hope that helps!

Regards,
Jean

[Inderjit Rai]

Hi Jean,

Thanks for your reply. Just another question about #2. I already
have the code written in a VBA module that works with an update query.
I have a button labelled "update" that transfers the form data to the
table. How do i call my code when this button is pressed? In the code,
how do i reference the fields values in the form?

Many Thanks,

Inderjit

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Inderjit Rai]

Hi Ed,

Thanks for your suggestions, i'll try that as soon as i get a
chance. I have already tried what you sugguested in #1 but when i do
that i can't add a button to do a record search.

Thanks,

Inderjit

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ed Robichaud]

There seems to be a basic misunderstandin g here. Most data entry forms have
a record source, so that the form's controls are "bound" to the underlying
table/query. You're working with a dynamic recordset and any
additions/edits/deletions are made immediately (actually, when Jet finds a
free moment) to the table/query. For that setup, there is no need to run
any update query or "transfer" data to the table. Access/Jet does all that
for you automatically.

The form's Allow Additions, Allow Edits, Allow Deletions, etc., properties
refer to the record source, NOT whether you can add/delete controls on the
form.

Look at Northwinds, or any of the template generated databases to see how
most developers and users set up data entry forms. Unbound forms and
controls are used mostly to capture user inputs that are not stored anywhere
and/or to show the results of calculations (that also should not be stored).
It is possible to set up unbound forms/controls for data entry, but why
spend all that time to try to fix something that's not broken?
-Ed

"Inderjit Rai" <in*********@ro gers.com> wrote in message
news:42******** @127.0.0.1...

Hi Jean,

Thanks for your reply. Just another question about #2. I already
have the code written in a VBA module that works with an update query.
I have a button labelled "update" that transfers the form data to the
table. How do i call my code when this button is pressed? In the code,
how do i reference the fields values in the form?

Many Thanks,

Inderjit

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Just to #1 -
In the form's OnLoad event, you can use code like this:
DoCmd.GoToRecor d , , acLast

HTH
- Turtle

"someguy" <in*********@ya hoo.com> wrote in message
news:Xn******** *************** **********@216. 196.97.142...

Hi,

I'm trying to accomplish the following on a form on which i have
removed navigational buttons (I apologize in advance if this is too many
questions);

The two easy ones are....
1) When the user opens the forms I would like it to open to a blank
form so they can create a new record (I don't want fields populated with
the first record from the table). The user should also have the option of
searching for a particular record.

2) Would like to do some data testing done on the data the user
entered in the forms before it updates the table. Basically i would like to concatenate a certain amount of zeros to what they entered using a loop.
Where do i place this code?

The more difficult one...
3) Would like to add a button to a form that would create and save a
CSV file by joining certain tables in the DB. The user should be prompted
to where he/she wants the file saved.

TIA