New Record Non-Traditionaly: Possible?

[Guest]

Hi All

We need to create new records in our 'Orders' table which consists of:
OrderID
CustomerID (linked to 'Customers' table)
DetailID (linked to 'OrderDetails' table which holds all products in an order)
...other various fields.

Traditionaly and very simply we could use a form based on the 'Orders' table, and because of the database relationships the ID fields would link to their corresponding tables to provide an appropriate value to complete the record.

However we're looking to achieve an "Order Entry" form differently:

To complete each field of the new record we would like a seperate form as if it was like a wizard.
1st Page - "Select Customer:" (form based on 'Customers' table as you navigate through customers)
2nd Page - "Select Products:" (form based on 'Products' table as you navigate through products and add to order as you go)
3rd Page - completes other various fields for the new record in the 'Orders' table (e.g. 'Date', 'OrderMethod', 'PaymentMethod', etc.)

Anyone have any hints as to how we can achieve this?

The initial idea we had was to create a duplicate table of the 'Orders' and 'OrderDetails' table which would only hold 1 record at a time, i.e. the new order. And with these tables the wizard forms would update the blank record inside with the ID values left on the form after navigatating through the possible records, i.e. 'CustomerID' & 'ProductID's, and eventually transfering this record into the primary 'Orders' and 'OrderDetails' tables clearing the duplicate tables after.

This made sense in theory but technically we couldn't accomplish it.

Anyone have any other ideas or guidance towards ours?

Please be advised that with regards to VBA & SQL we only have a working knowledge, and are new to most functions.
Your help would be very much appreciated. Thank you.

 

[PC Datasheet]

Before anything else, your tables are incorrect. There should not be a DetailID
field in the Orders table. Rather, there should be an OrderID in the
OrderDetails table. Your basic table structure should look like:

TblOrder
OrderID
CustomerID
OrderDate

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi All

We need to create new records in our 'Orders' table which consists of:
OrderID
CustomerID (linked to 'Customers' table)
DetailID (linked to 'OrderDetails' table which holds all products in an order)
...other various fields.

Traditionaly and very simply we could use a form based on the 'Orders' table,

and because of the database relationships the ID fields would link to their
corresponding tables to provide an appropriate value to complete the record.

However we're looking to achieve an "Order Entry" form differently:

To complete each field of the new record we would like a seperate form as if it was like a wizard.
1st Page - "Select Customer:" (form based on 'Customers' table as you navigate through customers)
2nd Page - "Select Products:" (form based on 'Products' table as you

navigate through products and add to order as you go)

3rd Page - completes other various fields for the new record in the

'Orders' table (e.g. 'Date', 'OrderMethod', 'PaymentMethod', etc.)

Anyone have any hints as to how we can achieve this?

The initial idea we had was to create a duplicate table of the 'Orders' and

'OrderDetails' table which would only hold 1 record at a time, i.e. the new
order. And with these tables the wizard forms would update the blank record
inside with the ID values left on the form after navigatating through the
possible records, i.e. 'CustomerID' & 'ProductID's, and eventually transfering
this record into the primary 'Orders' and 'OrderDetails' tables clearing the
duplicate tables after.

This made sense in theory but technically we couldn't accomplish it.

Anyone have any other ideas or guidance towards ours?

Please be advised that with regards to VBA & SQL we only have a working

knowledge, and are new to most functions.

 

[Marshall Barton]

We need to create new records in our 'Orders' table which consists of:
OrderID
CustomerID (linked to 'Customers' table)
DetailID (linked to 'OrderDetails' table which holds all products in an order)
...other various fields.

Traditionaly and very simply we could use a form based on the 'Orders' table, and because of the database relationships the ID fields would link to their corresponding tables to provide an appropriate value to complete the record.

However we're looking to achieve an "Order Entry" form differently:

To complete each field of the new record we would like a seperate form as if it was like a wizard.
1st Page - "Select Customer:" (form based on 'Customers' table as you navigate through customers)
2nd Page - "Select Products:" (form based on 'Products' table as you navigate through products and add to order as you go)
3rd Page - completes other various fields for the new record in the 'Orders' table (e.g. 'Date', 'OrderMethod', 'PaymentMethod', etc.)

Anyone have any hints as to how we can achieve this?

The initial idea we had was to create a duplicate table of the 'Orders' and 'OrderDetails' table which would only hold 1 record at a time, i.e. the new order. And with these tables the wizard forms would update the blank record inside with the ID values left on the form after navigatating through the possible records, i.e. 'CustomerID' & 'ProductID's, and eventually transfering this record into the primary 'Orders' and 'OrderDetails' tables clearing the duplicate tables after.

This made sense in theory but technically we couldn't accomplish it.

Anyone have any other ideas or guidance towards ours?

Please be advised that with regards to VBA & SQL we only have a working knowledge, and are new to most functions.
Your help would be very much appreciated. Thank you.

I would not use a bunch of independent forms to implement
your "wizard" type design. Instead, I suggest that you use
subforms on separate pages of a main form Tab control.

You can control which tab page is displayed at least as
easily as opening and closing forms, plus you gain back the
simplicity of using subforms. In case you are concerned
about displaying the selection tabs, note that the Style can
be set to None.

 

[Guest]

Thank you for your feedback but verification on our database layout was not what we asked.

We had the DetailID in the 'Orders' table as well as the OrderID in the 'OrderDetails' table, i.e. two fields connecting the tables unecessarily which has now been rectified.

Do you actually have any input towards the problem?

 

[Guest]

Thank you for your help. We appreciate this idea that you have submited to us, however it is is a little too vaigue for us to fully understand and consider implementing it, especially on the technical aspect.

What would these subforms be based on? How would the data from tab to tab gather to form a new order? Apart from the appearance and using one form, how would this be advatangerous towards solving the problem?

Please could you elaborate for us. Thanks.

 

[PC Datasheet]

It certainly hasn't been rectified because it's now incorrect! You should have
removed DetailID from the Orders table.

Thank you for your feedback but verification on our database layout was not what we asked.

We had the DetailID in the 'Orders' table as well as the OrderID in the

'OrderDetails' table, i.e. two fields connecting the tables unecessarily which
has now been rectified.

 

[Guest]

Look.

We're not interested in debating what is correct or incorrect with regards to our database layout. We have ackhnowledged your initial reply and upon which we have actually made a change as we have explained. Specifically which we didn't explain, is that the DetailID has been removed from 'Orders' table. It is now "obviously" correct so lets move on.

Now could you please answer us and kindly submit RELEVANT feedback if you have any.

 

[PC Datasheet]

You got all the feedback from me you're going to get and you benefited from
that. Your tables are now correct.

Your attitude stinks!!

Look.

We're not interested in debating what is correct or incorrect with regards to

our database layout. We have ackhnowledged your initial reply and upon which we
have actually made a change as we have explained. Specifically which we didn't
explain, is that the DetailID has been removed from 'Orders' table. It is now
"obviously" correct so lets move on.

 

[Guest]

FYI, the tables were not "incorrect" to begin with. They just had an additional and an uncecessary field which could have been removed to encourage simplicity.

For you to step in and pronounce that we have in your opinion made a mistake with something that is irrelevant to the subject post anyway is both unhelpful and annoying.

I think you need to have a look at your attitude my friend, or at least your perception. It seems that you've stepped in merly to point out something that others wouldn't for self promotion without any intention of helping at all.

 

[Larry Linson]

We need to create new records in our
'Orders' table which consists of:
OrderID
CustomerID (linked to 'Customers' table)
DetailID (linked to 'OrderDetails' table which holds all products in an order)
...other various fields.

Traditionaly and very simply we could
use a form based ...

However we're looking to achieve an
"Order Entry" form differently:

To complete each field of the new record we
would like a seperate form as if it was like a
wizard.
1st Page - "Select Customer:" (form based
on 'Customers' table as you navigate through
customers)
2nd Page - "Select Products:" (form based
on 'Products' table as you navigate through
products and add to order as you go)
3rd Page - completes other various fields for
the new record in the 'Orders' table (e.g. 'Date',
'OrderMethod', 'PaymentMethod', etc.)

Anyone have any hints as to how we can achieve this?

I'm not sure what advantage this would have. You can put a page break on a
form (at least you could back in Access 2.0) but working on an inherited
application back in those days, that proved clumsy and not nearly as
controllable as it might seem.

The only "good" way I have found to "page" in forms is with a Tab control.
In your case, I think separate forms for scrolling/navigating through the
customer table and the products table, imbedded in subform controls in the
tab pages would be as easy as any.

I've done similar forms, and never had a problem with design or with users,
"the easy way" you describe... a main form, with a ComboBox for choosing the
Customer (and a Subform for displaying customer detail) and a subform for
the order detail with a bound Combo Box for choosing the product, but
controls for entering quantity, etc..

Good luck with your project.

Larry Linson
Microsoft Access MVP

P.S. Although you didn't ask for a review of your table design, it is very
common for people to comment on table design that appears improper (and that
one _appeared_ as though it might be unworkable... with the foreign key in
the wrong table). Often, such comments are of more value to the poster than
the question they actually asked. It's certainly not something to get all
"huffy" about.

With the exception of a few Microsoft employees, everyone here who answers
questions does so as a volunteer (just FYI, MVPs are recognized by Microsoft
with that title for help given the user community, but are not Microsoft
employees, and not paid by Microsoft). No one is _obligated_ to answer any
post.

 

[Guest]

OK thanks Larry.

Is using the subforms on the tab control as you've described in order to complete a new order technically difficult? Is it likely that we will need any particular lines of code to accomplish any of the steps in the process? Any thing at all that we should look out for when attempting this approach?

 

[Marshall Barton]

Thank you for your help. We appreciate this idea that you have submited to us, however it is is a little too vaigue for us to fully understand and consider implementing it, especially on the technical aspect.

What would these subforms be based on? How would the data from tab to tab gather to form a new order? Apart from the appearance and using one form, how would this be advatangerous towards solving the problem?

Please could you elaborate for us. Thanks.

A tab control allows you to place numerous controls
(including subform controls) on a main form but only display
the ones on the current tab page. Your "wizard" Next button
then only has to change the active tab page instead of your
idea of opening separate forms. E.g.
Me.tabcontol = Me.tabcontol + 1

The Controls on the tab pages are still part of the main
form so most of your code does not need to even be aware of
the tab control. You did say that using subforms was the
traditional and easy way to the job and all I'm trying to
explain is that you can still do that while at the same time
make it look like a wizard to your users.

 

[Guest]

Oh yes I understand. That's great. Thank you Marshall.