Creating an enquiry form

[Flynners]

Hi
This is probably a really fundamental basic but I just am not seeing the wood for the trees at the moment.
I am using access2007 with SQL backend to record tender details.
I have a main table called tbltenders, pk in this table is tenderid. I have another table called tbltype, with typeid and typedesc fields. I have typeID in tbltenders.

I have other tables with supplier details, submission details, scores, marking scheme etc, with similar setups

The workflow of this would require that a record is created, and other data is not entered until later stages.

I want to create an enquiry form. My problem is that enquiry could be selected at any stage of the process.eg a query showing tbltenders details and typedesc (rather than typeID) from tbltype will only show results if type has been entered. I want the form to show as much info as has been entered yet, in a readable format rather than integers from tbltenders. If typedesc has not been entered the form won't show this record. I have many other instances like this such as awarded to, submissions, procedure used


Do I create temp tables, populate and then clear down when enquiry form is closed. Or is there an obvious "correct" way to do get my enquiry for no matter how much data has been entered todate.

Thanks in advance.

 

[jzwp22]

Typically you would base the form on just the one table (tbltenders). You would use a combo box based on the tender type table to select the type. You should be able to store the record without a type selected as long as it is not designated as a required field in tblTenders.

 

[Flynners]

thank you for your reply. i dont think it solves my problem though. When the user is entereing data they would select that from a combo box which refers to tbltype. this displays the typedesc for the user but writes the typeID to tbltender.

if i base my enquiry form just on tbltender i will only see 1 or 2 or 3 as the type instead of the description which would be "Goods" or "Services" or "Works". If i base it on a query, i only see results that meet query criteria.

If it was just this one field i could probably put in text box - if type =1 then populate textbox1 with "Goods" etc but i have alot of different combo boxes at data entry stage, with just integers in tblTenders

I dont want to over complicate the loading of this form and the time it takes to lookup other tables.

thanks again

 

[jzwp22]

if i base my enquiry form just on tbltender i will only see 1 or 2 or 3 as the type instead of the description which would be "Goods" or "Services" or "Works". If i base it on a query, i only see results that meet query criteria.

By chance do you have a lookup field in your tbltenders?

You can adjust what is seen in the combo box by adjusting the column widths property of the combo box. You will need to adjust the width to zero for the ID field (the one providing the numbers). The column with the first non-zero width will be displayed in the combo box.

So if the row source of the combo box looks like this:

SELECT typeid, typedesc
FROM tbltype

The column widths property should look like this: 0", 1.5" (just some non-zero value)

 

[Flynners]

thank you very much.
combo boxes worked.

i think i have the same problem with reports now if data entry is incomplete, but thats for a different forum, for another day

 

[jzwp22]

For the report you will have to create a query that pulls in the text fields of the various tables. You will have to adjust the join types in that query to include left joins (I believe that is join type option 2) from your main table to the tables with the related information that may or may not have been defined for a record.

 

[Simon_MT]

A form can be based on a Query involving one or many tables involved. Whilst these type Forms and grabbing descriptors from Foreign tables you can still use combiboxes.

My view with combiboxes is the combiboxes allows changes to Active entities whilst Archive or old entities are ignored. You have tenders that are closed so these are removed from use in the combi-boxes in the future. The combi-boxes are filtered.

The underlying Query on the other-hand is unfiltered and every Entity will appear.

Simon