Retrieving auto number value immediately

[millie.patel]

Hi,

I am working on a form right now.
I have three tables:
tbl_projects
--projectID
--projectname
--projectdescription
--projectmanager
tbl_products
--productID
--productname
--productshortname
--productcategory
tbl_projectproducts
--projectID
--productID

The form is used to enter a project, it has multiple fields (i.e.
project name, project manager, project description).

Only when the project is entered into the database, can I retrieve the
autonumber (primary key) to use to insert values into the
projectproducts table.

Is there a way to retrieve the value on the same form? so that i can
enter the product info right away, besides going to a new page?

thanks!

 

[strive4peace]

Hi Millie,

you can force a save like this:

if me.dirty then me.dirty = false

if you are using the AfterUpdate event of a control (perhaps
projectname), it is obviously dirty, so you can do this:

me.dirty = false
msgbox me.id_controlname

even though you may have more to fill out, you can get the
ID right away (as long as you don't have other fields that
need to be filled before the record saves)

just out of curiosity, why do you need the ID? Why not have
tbl_projectproducts be the recordsource for a subform with
projectID in LinkMasterFields and LinkChildFields?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Graham Mandeno]

Hi Millie

The autonumber value should be created and assigned the moment you begin to
create a new record. You don't need to wait until it is saved. The earliest
you can get it then is the form's BeforeInsert event.

The most common way to handle a setup like this is with a form and subform.
The form is bound to tbl_projects and the subform to tbl_projectproducts.
The subform should be continuous and needs to contain only a combo box bound
to productID, with its RowSource based on tbl_products. Set both the
LinkMasterFields and LinkChildFields for your subform control to projectID.

Then you can add a new project and select associated products, all from the
one form.

 

[strive4peace]

In my version, the autonumber value is not available on the
BeforeInsert*, but it is on the BeforeUpdate event...it is
also available on the first control's BeforeUpdate and, of
course, AfterUpdate events

* I thought it would be since, in code, you can get it as
soon as you do .AddNew, but I tried that before I posted
previously and it doesn't apparently work the same way with
the form event -- kinda makes sense though, since it is
before the record is inserted

'~~~~~~

creating a continuous form with checkboxes to pick products

To add on to what Graham said about the form setup.
Handling the records in a continuous subform is, of course,
the way to go. Another thing I often like to do is
something like this:

subform RecordSource:
SELECT pp.projectID, pp.productID,
p.productname, p.productshortname,
p.productcategory,
IIF(isnull(pp.productID),false,true) as chkUsed
FROM tbl_projectproducts as pp
RIGHT JOIN tbl_products as p
ON pp.productID = p.productID
WHERE pp.projectID = forms!mainform!projectID
OR pp.projectID Is Null

chkUsed will be a checkbox at the beginning of each row.
Every product will be listed and the ones that are actually
records in tbl_projectproducts will be checked.

The form itself will not be updateable, but you can use the
click event of the checkbox to append or delete records from
tbl_projectproducts and then requery the form

I am not quite sure about the SQL since I didn't test it --
you may need to make a couple of adjustments


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Graham Mandeno]

Hi Crystal

chkUsed will be a checkbox at the beginning of each row. Every product
will be listed and the ones that are actually records in
tbl_projectproducts will be checked.

The form itself will not be updateable, but you can use the click event of
the checkbox to append or delete records from tbl_projectproducts and then
requery the form

This is precisely what I've been doing since Access 2.0.

Who needs "complex data" eh?

Cheers,
Graham

 

[millie.patel]

thanks all! all your messages were very very helpful!

 

[strive4peace]

you're welcome, Millie

if you are going to use the checkboxes, you should add this
field to tbl_projectproducts

ProjProdID, autonumber

this will give you a unique way to identify each record

in the recordset, you can use this:

IIF(IsNull(ProjProdID),0,ProjProdID)

and then, if ProjProdID = 0, you know you need to add the
record when the checkbox is ticked, or if >0, remove it when
the checkbox is ticked ... then, after you issue SQL:

currentdb.tabledefs.refresh
DoEvents
me.chkUsed.requery


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

Hi Graham,

I love Access! I, too, have been using it since 2.0 -- and
continue to learn great new things all the time!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[millie.patel]

i could have subforms -- but was just wondering if there was a
different way of doing it -- im not too familiar with using subforms

but all your information was helpful!
thanks!

 

[strive4peace]

Hi Millie,

There are other ways, but knowing how to use subforms is
important. Subforms would be the easiest way.

The subform is created just as a form is -- it is called a
subform because of the way it is used.

make a from based on tbl_projectproducts

DefaultView --> Continuous Forms

since this will be used as a subform, you would have the
projectID field and its Visible property could be No (you
will not need it to show)

When I have controls that are not visible, I make the
backcolor black and the text white so they stand out more
when you are in the design view

Then, as Graham said, you could use a combobox for ProductID

Name --> ProductID
RowSource -->
SELECT productID, productname
FROM tbl_products
ORDER BY productname
ColumnCount --> 2
ColumnWidths --> 0;2
ListWidth --> 2
(this adds up to the sum of the column widths)

since the ID column will have a width of 0, it won't show,
but that is what will be stored

The main form will be based on tbl_projects

to this form, add a subform control

SourceObject --> formname for tbl_projectproducts
LinkMasterFields --> projectID_controlname
LinkChildFields --> projectID_controlname

the link fields must be ON the respective forms

since you are new to this, forget about the checkbox
stuff... you can print that information and read it later
when you are more comfortable with subforms

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*