Form with multiple tables attached

Hi, im trying to create a prototype application in MS Access that I
will later port to VB.Net.

Essentially it is a Tendering System that will allow Tender Managers
to Create Request For Tenders(RFT), Request for Quotes(RFQ),
Registration of Interests (ROI)etc etc. End users will be able to
compile responses back and it will al be completed electronically via
a e-Tender Box. At least that is what I HOPE to achieve.

What I would like to create a single MS Access form where the user (in
this case the Tender Manager i e the guy requesting tenders) can
select the type of document he wants to create and then the remainder
of the input (text) boxes etc will display depending on the document
selected.

The best ( and briefest) example would be that the document ID would
change depending on the type the user would like to create ie. if they
wanted an RFT then the next RFT number in sequence would appear say
RFT1023. However, if the next document the user created was an ROI
then that number would populate the form - say ROI589.

Is this possible?

Anyt thoughts would be greatly appreciated.

Re: Form with multiple tables attached

On Jan 9, 11:59=A0pm, Bails wrote:
> Hi, im trying to create a prototype application in MS Access that I
> will later port to VB.Net.
>
> Essentially it is a Tendering System that will allow Tender Managers
> to Create Request For Tenders(RFT), Request for Quotes(RFQ),
> Registration of Interests (ROI)etc etc. End users will be able to
> compile responses back and it will al be completed electronically via
> a e-Tender Box. At least that is what I HOPE to achieve.
>
> What I would like to create a single MS Access form where the user (in
> this case the Tender Manager i e the guy requesting tenders) can
> select the type of document he wants to create and then the remainder
> of the input (text) boxes etc will display depending on the document
> selected.
>
> The best ( and briefest) example would be that the document ID would
> change depending on the type the user would like to create ie. if they
> wanted an RFT then the next RFT number in sequence would appear say
> RFT1023. However, if the next document the user created was an ROI
> then that number would populate the form - say ROI589.
>
> Is this possible?
>
> Anyt thoughts would be greatly appreciated.

I have some thoughts along with some caveats. In general, it is best
to avoid putting two kinds of things into a single field. A field
makes a lousy database. Managers, however, like being able to look at
a field and get a little extra information. Plus, I suppose that
seeing an ROI document in a directory for RFT documents would alert
users that there is a problem. Storing the document type and using an
AutoNumber with that would cause potentially undesirable gaps in the
set of document names. It would be possible to find the largest
number used so far for a particular document type, but that can lead
to problems if multiple users are using the database simultaneously.
For example, something like:

SELECT "RFT" & CStr(Max(Right(DocumentName, Len(DocumentName) - 3)) +
1) AS NewDocumentName FROM tblDocumentNames WHERE Left(DocumentName,
3) =3D "RFT";

or

SELECT Forms!frmDocPicker!cbxDocumentType.Value &
CStr(Max(Right(DocumentName, Len(DocumentName) - 3)) + 1) AS
NewDocumentName FROM tblDocumentNames WHERE Left(DocumentName, 3) =3D
CStr(Forms!frmDocPicker!cbxDocumentType.Value);

should be able to find the next document name (make sure a document
type value exists before running the query). But two users might grab
the same name. Even a separate table containing the document type
abbreviations and the maximum number used so far for that type can
lead to the same problem. But such a separate table wouldn't have to
wait for a query that will take potentially longer and longer to get
the result. That could mitigate chances for a collision. I suppose
it would be wise to find out for sure whether or not multiple Tender
Managers will be using the database at the same time.

This all reminds me of a bad database decision I once made. I have
some databases that produce PDF files. Originally the files were
simply there to let the user print the file and verify that the input
was correct before posting the input to the database. The PDF file
was to be regenerated if needed from the database -- or so I thought.
The company's job processing people decided to use the PDF files to
document and manage spending for the jobs rather than use the normal
Access reports designed for that purpose. The PDF files of a given
kind go into a single shared directory. They are named with a job
number and two digits after a hyphen (e.g., 18945-03). When a job
number is chosen from a combobox, a listbox reads the directory and
shows all the documents corresponding to that job number. Suppose you
need to find the next available two digit number, either the max plus
one or the first open number. It gets really ugly (i.e., slower and
slower response time) using the directory contents once you get
several thousand documents in the directory even with the indexing
service. Directories also, make lousy databases. The database should
have been the only place to determine the next two digit number.
There's a distinction between creation and posting but the tables can
handle that easily. Plus, the information from any posted files can
append or overwrite data in order to modify or duplicate contents. I
marvel at how they decided to use the database.

I hope this helps,

James A. Fortune
CDMAPoster@FortuneJames.com

Re: Form with multiple tables attached

On Jan 11, 7:58=A0pm, CDMAPos...@fortunejames.com wrote:
> On Jan 9, 11:59=A0pm, Bails wrote:
>
>
>
>
>
> > Hi, im trying to create a prototype application in MS Access that I
> > will later port to VB.Net.
>
> > Essentially it is a Tendering System that will allow Tender Managers
> > to Create Request For Tenders(RFT), Request for Quotes(RFQ),
> > Registration of Interests (ROI)etc etc. End users will be able to
> > compile responses back and it will al be completed electronically via
> > a e-Tender Box. At least that is what I HOPE to achieve.
>
> > What I would like to create a single MS Access form where the user (in
> > this case the Tender Manager i e the guy requesting tenders) can
> > select the type of document he wants to create and then the remainder
> > of the input (text) boxes etc will display depending on the document
> > selected.
>
> > The best ( and briefest) example would be that the document ID would
> > change depending on the type the user would like to create ie. if they
> > wanted an RFT then the next RFT number in sequence would appear say
> > RFT1023. However, if the next document the user created was an ROI
> > then that number would populate the form - say ROI589.
>
> > Is this possible?
>
> > Anyt thoughts would be greatly appreciated.
>
> I have some thoughts along with some caveats. =A0In general, it is best
> to avoid putting two kinds of things into a single field. =A0A field
> makes a lousy database. =A0Managers, however, like being able to look at
> a field and get a little extra information. =A0Plus, I suppose that
> seeing an ROI document in a directory for RFT documents would alert
> users that there is a problem. =A0Storing the document type and using an
> AutoNumber with that would cause potentially undesirable gaps in the
> set of document names. =A0It would be possible to find the largest
> number used so far for a particular document type, but that can lead
> to problems if multiple users are using the database simultaneously.
> For example, something like:
>
> SELECT "RFT" & CStr(Max(Right(DocumentName, Len(DocumentName) - 3)) +
> 1) AS NewDocumentName FROM tblDocumentNames WHERE Left(DocumentName,
> 3) =3D "RFT";
>
> or
>
> SELECT Forms!frmDocPicker!cbxDocumentType.Value &
> CStr(Max(Right(DocumentName, Len(DocumentName) - 3)) + 1) AS
> NewDocumentName FROM tblDocumentNames WHERE Left(DocumentName, 3) =3D
> CStr(Forms!frmDocPicker!cbxDocumentType.Value);
>
> should be able to find the next document name (make sure a document
> type value exists before running the query). =A0But two users might grab
> the same name. =A0Even a separate table containing the document type
> abbreviations and the maximum number used so far for that type can
> lead to the same problem. =A0But such a separate table wouldn't have to
> wait for a query that will take potentially longer and longer to get
> the result. =A0That could mitigate chances for a collision. =A0I suppose
> it would be wise to find out for sure whether or not multiple Tender
> Managers will be using the database at the same time.
>
> This all reminds me of a bad database decision I once made. =A0I have
> some databases that produce PDF files. =A0Originally the files were
> simply there to let the user print the file and verify that the input
> was correct before posting the input to the database. =A0The PDF file
> was to be regenerated if needed from the database -- or so I thought.
> The company's job processing people decided to use the PDF files to
> document and manage spending for the jobs rather than use the normal
> Access reports designed for that purpose. =A0The PDF files of a given
> kind go into a single shared directory. =A0They are named with a job
> number and two digits after a hyphen (e.g., 18945-03). =A0When a job
> number is chosen from a combobox, a listbox reads the directory and
> shows all the documents corresponding to that job number. =A0Suppose you
> need to find the next available two digit number, either the max plus
> one or the first open number. =A0It gets really ugly (i.e., slower and
> slower response time) using the directory contents once you get
> several thousand documents in the directory even with the indexing
> service. =A0Directories also, make lousy databases. =A0The database should=

> have been the only place to determine the next two digit number.
> There's a distinction between creation and posting but the tables can
> handle that easily. =A0Plus, the information from any posted files can
> append or overwrite data in order to modify or duplicate contents. =A0I
> marvel at how they decided to use the database.
>
> I hope this helps,
>
> James A. Fortune
> CDMAPos...@FortuneJames.com- Hide quoted text -
>
> - Show quoted text -

Thanks that does help alot.

Remember that this is a Prototype only and I imagine it will only
manage a few dozen RFx documents as I am developing it.

My ultimate goal is to get it into a .Net environment (or equivalent)
so as to make it a stand alone .exe.

Whilst I have some .Net (VB) skills I suspect I will need to pass it
off to a professional developer to put it together professionally.

Thanks again for you help.

Re: Form with multiple tables attached

On Jan 11, 7:58=A0pm, CDMAPos...@fortunejames.com wrote:
> On Jan 9, 11:59=A0pm, Bails wrote:
>
>
>
>
>
> > Hi, im trying to create a prototype application in MS Access that I
> > will later port to VB.Net.
>
> > Essentially it is a Tendering System that will allow Tender Managers
> > to Create Request For Tenders(RFT), Request for Quotes(RFQ),
> > Registration of Interests (ROI)etc etc. End users will be able to
> > compile responses back and it will al be completed electronically via
> > a e-Tender Box. At least that is what I HOPE to achieve.
>
> > What I would like to create a single MS Access form where the user (in
> > this case the Tender Manager i e the guy requesting tenders) can
> > select the type of document he wants to create and then the remainder
> > of the input (text) boxes etc will display depending on the document
> > selected.
>
> > The best ( and briefest) example would be that the document ID would
> > change depending on the type the user would like to create ie. if they
> > wanted an RFT then the next RFT number in sequence would appear say
> > RFT1023. However, if the next document the user created was an ROI
> > then that number would populate the form - say ROI589.
>
> > Is this possible?
>
> > Anyt thoughts would be greatly appreciated.
>
> I have some thoughts along with some caveats. =A0In general, it is best
> to avoid putting two kinds of things into a single field. =A0A field
> makes a lousy database. =A0Managers, however, like being able to look at
> a field and get a little extra information. =A0Plus, I suppose that
> seeing an ROI document in a directory for RFT documents would alert
> users that there is a problem. =A0Storing the document type and using an
> AutoNumber with that would cause potentially undesirable gaps in the
> set of document names. =A0It would be possible to find the largest
> number used so far for a particular document type, but that can lead
> to problems if multiple users are using the database simultaneously.
> For example, something like:
>
> SELECT "RFT" & CStr(Max(Right(DocumentName, Len(DocumentName) - 3)) +
> 1) AS NewDocumentName FROM tblDocumentNames WHERE Left(DocumentName,
> 3) =3D "RFT";
>
> or
>
> SELECT Forms!frmDocPicker!cbxDocumentType.Value &
> CStr(Max(Right(DocumentName, Len(DocumentName) - 3)) + 1) AS
> NewDocumentName FROM tblDocumentNames WHERE Left(DocumentName, 3) =3D
> CStr(Forms!frmDocPicker!cbxDocumentType.Value);
>
> should be able to find the next document name (make sure a document
> type value exists before running the query). =A0But two users might grab
> the same name. =A0Even a separate table containing the document type
> abbreviations and the maximum number used so far for that type can
> lead to the same problem. =A0But such a separate table wouldn't have to
> wait for a query that will take potentially longer and longer to get
> the result. =A0That could mitigate chances for a collision. =A0I suppose
> it would be wise to find out for sure whether or not multiple Tender
> Managers will be using the database at the same time.
>
> This all reminds me of a bad database decision I once made. =A0I have
> some databases that produce PDF files. =A0Originally the files were
> simply there to let the user print the file and verify that the input
> was correct before posting the input to the database. =A0The PDF file
> was to be regenerated if needed from the database -- or so I thought.
> The company's job processing people decided to use the PDF files to
> document and manage spending for the jobs rather than use the normal
> Access reports designed for that purpose. =A0The PDF files of a given
> kind go into a single shared directory. =A0They are named with a job
> number and two digits after a hyphen (e.g., 18945-03). =A0When a job
> number is chosen from a combobox, a listbox reads the directory and
> shows all the documents corresponding to that job number. =A0Suppose you
> need to find the next available two digit number, either the max plus
> one or the first open number. =A0It gets really ugly (i.e., slower and
> slower response time) using the directory contents once you get
> several thousand documents in the directory even with the indexing
> service. =A0Directories also, make lousy databases. =A0The database should=

> have been the only place to determine the next two digit number.
> There's a distinction between creation and posting but the tables can
> handle that easily. =A0Plus, the information from any posted files can
> append or overwrite data in order to modify or duplicate contents. =A0I
> marvel at how they decided to use the database.
>
> I hope this helps,
>
> James A. Fortune
> CDMAPos...@FortuneJames.com- Hide quoted text -
>
> - Show quoted text -

Also just re-reading your reply. It seems to me that you are under the
impression that all the documents that are created will be on the 1
database.

I was thinking of having a seperate database for each type of document
and then filling the form based on each document type requirements.

For example I would have a table each for RFT (tblRFT), EOI (tblEOI)
and then address them depending on the selection.

The other thought I have is to simply have a form for each doc type.
Whilst this is much, much easier to set up, its a little more work
foor the end user to make sure they are in the correct area (ie they
dont start creating a RFQ when they really want a EOI).

The other factor I would need t oconsider is that, often EOI's will
turn into an RFT or RFQ, so I will need to be able to convert the
document type quickly and easily.

You also asked how many end users would utilise this, generally it
will only be 1 person, however occasionally it may be up to say 5
people depending on the size of the procurement department.

Once again thanks for your reply.

Re: Form with multiple tables attached

On Jan 13, 8:19=A0pm, Bails wrote:

> For example I would have a table each for RFT (tblRFT), EOI (tblEOI)
> and then address them depending on the selection.

I considered that as a possible solution. Instead of thinking that
through on my own, I notice that Allen Browne recently made a post
about the same problem:

http://groups.google.com/group/microsoft.public.access/msg/7 32bb53103e4c848

James A. Fortune
CDMAPoster@FortuneJames.com