Form with Multiple Cascading Combos, and an Add New problem

[Holly]

Hi guys. I need some advice. I've been wasting a lot of time trying
different approaches, but I thought I'd ask some experts for opinions.
I have a Form which access one table.
The table is not really normalized, it has a Document number as a key,
broken down into 2 columns Group and Instruction. There can also be
multiple Item numbers because there needs to be a Revision history with
a 2 digit revision number, so I could have:

Document Group Inst Revision #
123.01-001 123.01 001 00

The revision numbers can increment by 1 for each change to the
Document. They have one big form to maintain the table. The users can
either Add, Change or just browse through
the form. The form displays some other detail info that is also listed
in the main table (like Title, Training (Y/N), Status, etc).

So I added a Group and Inst cascading combo box. The cbo1 (group) box
does a RowSource query on the Enter event, "Select Distinct Group from
tblX;" Then the cbo2 (Instruction #) box looks up a RowSource on the
enter event, "select distinct Instnbr from tblX where
(((Group)=[cbo1])) Order By Instnbr;"

I have a NotinList on cbo2, so the user can enter new Instruction
Numbers (the groups will stay the same so I don't need one for cbo1).
Then I also have an AfterUpdate on cbo2 to do a FindFirst to retrieve
the values the user picks from combo1 & combo2, and place a bookmark
there. Everything seems to work ok, except when I need to add a new
record.
The problem is the Revision number. The program is adding one, to
increment the revision number automatically when the user clicks the
Add button and goes to select the combo boxes for that Group and
Instruction number. The table is sorted by Group asc, Inst asc and
Revision# descending. The find first gets the latest Revision number,
which is all the user wants to see in the form. But if they need to do
an Add for that given cbo1 & cbo2 they searched for, to add a new
revision number, that needs to auto increment by 1. I can't
autoincrement the field in the table because it is unique to each group
& instruction (or document).

I tried doing an AddNew in the AfterUpdate of combo2, by setting a flag
(I'm a mainframe programmer, so I know this is bad) when the Click is
done on the Add button. If that value is true, it adds 1 to the
existing revision number. However, the form updates before the user
has had time to enter the new fields, which have been filled in with
the bookmarked instance of the group and Instruction number. The
revision number is saved over the existing record because of the darn
bookmark.

Can anyone suggest how I can add a new record after an AfterUpdate,
bookmark for a multi combo box?
Thank you!

 

[ManningFan]

Why not check the revision, and only bookmark if the revision doesn't
already exist? Don't be afraid to use a DLookup inside an If, Then,
Else statement at the top of your AfterUpdate code.

Hi guys. I need some advice. I've been wasting a lot of time trying
different approaches, but I thought I'd ask some experts for opinions.
I have a Form which access one table.
The table is not really normalized, it has a Document number as a key,
broken down into 2 columns Group and Instruction. There can also be
multiple Item numbers because there needs to be a Revision history with
a 2 digit revision number, so I could have:

Document Group Inst Revision #
123.01-001 123.01 001 00

The revision numbers can increment by 1 for each change to the
Document. They have one big form to maintain the table. The users can
either Add, Change or just browse through
the form. The form displays some other detail info that is also listed
in the main table (like Title, Training (Y/N), Status, etc).

So I added a Group and Inst cascading combo box. The cbo1 (group) box
does a RowSource query on the Enter event, "Select Distinct Group from
tblX;" Then the cbo2 (Instruction #) box looks up a RowSource on the
enter event, "select distinct Instnbr from tblX where
(((Group)=[cbo1])) Order By Instnbr;"

I have a NotinList on cbo2, so the user can enter new Instruction
Numbers (the groups will stay the same so I don't need one for cbo1).
Then I also have an AfterUpdate on cbo2 to do a FindFirst to retrieve
the values the user picks from combo1 & combo2, and place a bookmark
there. Everything seems to work ok, except when I need to add a new
record.
The problem is the Revision number. The program is adding one, to
increment the revision number automatically when the user clicks the
Add button and goes to select the combo boxes for that Group and
Instruction number. The table is sorted by Group asc, Inst asc and
Revision# descending. The find first gets the latest Revision number,
which is all the user wants to see in the form. But if they need to do
an Add for that given cbo1 & cbo2 they searched for, to add a new
revision number, that needs to auto increment by 1. I can't
autoincrement the field in the table because it is unique to each group
& instruction (or document).

I tried doing an AddNew in the AfterUpdate of combo2, by setting a flag
(I'm a mainframe programmer, so I know this is bad) when the Click is
done on the Add button. If that value is true, it adds 1 to the
existing revision number. However, the form updates before the user
has had time to enter the new fields, which have been filled in with
the bookmarked instance of the group and Instruction number. The
revision number is saved over the existing record because of the darn
bookmark.

Can anyone suggest how I can add a new record after an AfterUpdate,
bookmark for a multi combo box?
Thank you!

 

[Holly]

Why not check the revision, and only bookmark if the revision doesn't
already exist? Don't be afraid to use a DLookup inside an If, Then,
Else statement at the top of your AfterUpdate code.

Hey there - hope you are either a UT fan or Colts. Anyhoo,
the revision is always going to be there. I'm picking only the latest
one,
because there could be 99 in there for that cbo1 & cbo2.
Where should I check the revision? The user is always going to select
cbo boxes 1, then 2 as search criteria, to bring up the record. It's
only if they need to add a new revision that the problem happens,
because due to the searching thru the combos, the bookmark is
necessary. Does that make sense or am I delusional? It's probably the
latter.

 

[ManningFan]

Indy fan, although I'm closer to NY and now the Giants have a Manning
too. )

How large are your datasets? We use Access as a front end with a SQL
Server backend and we literally have over 500M records in some tables
and we never bookmark.

If there's always a revision then how do you know when to add a new
record? Maybe I don't understand the original question as well as I
thought, but I was proposing this (assuming my assumptions are
correct):

The user enters a revision into cbo2, and the revision doesn't already
exist in the database
AfterUpdate you look at the value of cbo2 and DLookup it against your
table.
If the value already exists, proceed as normal.
If the value doesn't exist in the table, assume it's an AddNew and
circumvent the bookmark.

Am I totally off the mark? Should I put the crackpipe down?

 

[Holly]

Hey there!

Sorry I didn't reply yet - I was so ready to leave Friday afternoon -
I hope you're having a good holiday weekend!

The database is teeny tiny compared to all that! It was pretty messed
up
when I started working on it, most of the reports didn't work (it was
written in
2.0 and never properly upgraded) so its been a real experience so far!

I think I didn't explain totally right, there are two cascading combo
boxes, you select one, then based on that selection you get a list of
the numbers in cbo2 (they can use a not in list event to type in their
own cbo2 instruction number). If they enter a new cbo2 value, then the
revision number, a third field, is automatically revision 00. So the
number would be like 001.01-001, Revision 00. 001.01 is cbo1 Group,
which has x number of instructions (001-999), and these all can be
revised up to 99 times.

So the program has to know that when a user clicks "add" for that cbo1
& cbo2, which they've already selected in the form and the form shows
that record they searched for. Only on an Add, it should automatically
increment the revision number by 1. They don't want to have to type in
a revision number or leave that as an option. Which makes this a lot
harder I guess....

Just remember, Hugs not Drugs. (just being cheesy).

 

[Holly]

Hey there!

Sorry I didn't reply yet - I was so ready to leave Friday afternoon -
I hope you're having a good holiday weekend!

snipped

Hey there!
Well I think the long weekend did me good, because I realized that it
was just where the bookmark was placed that was causing the problem. I
put an if statement before the bookmark to Ricki Lake "don't go there -
snap!" if the record is an Add...

Set rs = Me.RecordsetClone 'find the last group, inst# record in
table
rs.FindFirst _
"[GROUP] = " & Me![cbo1] & _
" AND " & _
"[InstNbr] = '" & Me![cbo2] & "'"
If AddRec = False Then
Me.Bookmark = rs.Bookmark
Else 'set next revision level up by 1 if an Add
If Me![REVLEVEL] = 0 Then
Me![REVLEVEL] = 1
Else
Me![REVLEVEL] = Me![REVLEVEL] + 1
End If
AddNewRecord
End If

And it seems to work for now. It may be inefficient and cheesy, but it
looks like it does what they want it to do.
Thank you for all your help. It gave me some good ideas about how to
do this better the next time!