To open a subform only displaying a new record

[Guest]

Please help!!

I would like to open a new record (ADD record) for update via subform
without the subform displaying any existing records (only blank record for
specific selection). I have created a form with relevant combo boxes to
select the relevant store to update and use a command button to link the
parent forms key criteria with the sub form. Anyones assitance will be
appreciated. Regards

 

[Guest]

George,

You need to set the Data Entry property of the form that displays in your
sub form holder to True. You may also wish to consider whether you want to
set the Cycle property to Current Record.

Regards,

Rod

 

[Guest]

So simple. Rod you are a star and thank you for responding so quick.
I have another question or two, was wondering if you could assist me.

Thanks again!!!!

 

[Guest]

Ask away!

So simple. Rod you are a star and thank you for responding so quick.
I have another question or two, was wondering if you could assist me.

Thanks again!!!!

 

[Guest]

Thanks Rod, really appreciate your assistance. The following questions:

1). I want to send an email to specific recipients via command button after
adding a new record on a sub form. The sub form contains text boxes and
comment boxes as well as combo boxes. Ideally i would like to automate the
Recipient/s (without having to select via SENT TO inmessage), The Subject,
and Body containg dates and comments.

2). I want to use autonumber, however would like to use custom prefix i.e.
A1000001. How do i set the input mask or whatever other object properties to
do the same?

3). I have two tables i.e. Region and Stores. Via combo box in a form i want
to select a store, however the combo box must only list the stores relevant
to a specific region and not all stores in the table. How do i get the combo
box to only list the grouped stores to a specific region?

I would really appreciate if you could assist with step by step method. Your
assistance is appreciated

Kindest regards

 

[Guest]

Hi Again George,

1. I suggest you investigate the SendObject method in VBA and/or the
SendObject action for macros. Both rely on you having a mail client known to
Windows - you probably use Outlook or Outlook Express so make sure one or the
other is defined as your default mail client. I can't offhand remember where
this is done but it's probably somewhere around the Windows Control Panel.
Try the acSendNoObject object type if all you want to do is send a message.
I've had reasonable success with this in the past although I seem to remember
Access v2000 dropped the subject line no matter what I tried.

You will also have to suffer the "A program is attempting ..." type of
Windows pop up messages when using any automated mail procedure, a
consequence of abuses by our less reputable bretheren.

If you use Oulook then you can get really clever by opening an Outlook
session and using Outlook data for your addresses, etc.

Whatever you choose I think you will need to write some VBA to a greater or
a lesser extent.

2. Don't know! Something that may satisfy you is to define an autonumber
(long integer) and a prefix (with the default value set to your prefix value)
in your table design. Then you can concatenate them into a string for many
purposes:

strFullId = strPrefix & CStr(Id) - or format the Id if you want leading
zeros

or simply adjoin the two fields on forms and reports (remove the labels as
necessary and left justify the autonumber. With a little experimenting and
playing around the user won't even see the join. The latter method is
particularly apt for form entry of new records.

3. Yes, this is a common and easily fulfilled requirement. You need to poke
different Row Sources in the Combo Box listing your stores. The times to do
this are On Load for the form and On Change for the region Combo Box. As
usual with all synchronization requirements the bit in the middle is easy and
you need to worry about the starting and/or ending conditions. On Load
should take care of the starting condition and I cannot see a need for any
special ending condition. There's not enough space here to give you all the
code examples, etc. so I will email you a sample mdb that does the job.

Regards,

Rod

 

[Ron2006]

3. Yes, this is a common and easily fulfilled requirement. You need to poke
different Row Sources in the Combo Box listing your stores. The times to do
this are On Load for the form and On Change for the region Combo Box. As
usual with all synchronization requirements the bit in the middle is easy and
you need to worry about the starting and/or ending conditions. On Load
should take care of the starting condition and I cannot see a need for any
special ending condition. There's not enough space here to give you all the
code examples, etc. so I will email you a sample mdb that does the job.

If you use the onchange event it will execute the requery after EVERY
key stroke.
If you use the same code as he suggests but put it in the afterupdate
event it will execute it only once using the entire field not part of
it.

Ron

 

[Guest]

Ron,

Absolutely right! Of course I meant After Update, apologies for any
confusion caused.

Regards,

Rod

 

[Ron2006]

I have found that the answer and question (really) is always clearer
AFTER if have hit the "Post Message" button.


{along with all of the spelling errors (rather typos) ;-) }

Ron