Over did my Combo skills

[cous261]

Need help in get data back to form. I've created a form based off a couple of queries so that I could display one thing and store another. Example: Pulling in invoice information I store the recordId and Display Inv# Custname and Businessname.

Store in table Display on form (use form for printout too)

RecID Inv# CustName BusNam
449 9999 John Doe Walmart

Then 4 combos (same combo list) from another query does same thing on form to record why information returned to auditor. This allow four reason why the information is returned and is store in four different field Infoneed1, infoneed2, etc.. There below that is a date return and date completed field and a note field.

If you choice combo infoneed1 field you get a list of 7 plus reasons what is need to complete paper work and a code number is assign each one which is what I store in the table while displaying the description of the reason on the form. Example:

You have Inv#, custnam, busnam on top 2 rows
then

Info Need label
Infoneed1 - INfoneed4 text boxes
Infoneed1box (displayed) Code for it stored
Need Original Signature 1
Infoneed2box (displayed) Code for it stored
Need Supervisors Signature 2

Then you have date returned, completed date, notes fields at bottom of form.

So what I have is a table to store this info(InvReturntbl) in with just codes to run counts on for a report. Only when info comes back I need a query that pull it back up like I input it but I can't get what I want, I can get a selection combo to display back INV#, custnam, Busnam fields from stored rec# from combo 1 but can get the others to display when I use the selection combo created to find the record from the InvReturntbl.

Here's InvReturntbl data and then what I get from new form which is what I trying to build using query so when it paperwork comes back for completion date completed and notes field can be updated but all other info is locked.

Tbl
rec#(an) InvRecNum(text) INfo1 INfo2..Etc Rdate Cdate NOtes
1 449 1 2 0/0/00 text


Return form

INv# 9999
CustNam john doe BusNam Walmart

Shows Want
Infoneed1 1 Need Original Signature
Infoneed2 2 Need Supervisors Signature

Rdate 02/01/2007 CDate (Can't edit)
Notes
Returned to Bob for additional info (can't edit)

Any help would greatly be appericated as this has been a brain killer for over a week now and I'm just not getting something. All I like in completing this app is this, the report, and why my print current form is printing all forms and not just 2 copies of current one. YOu guys have been the best for answers in the past and I know someone is just waiting to make this simple as I bet I'm building/built giant when I needed a dwarf. Help put out my smking brain please. %%%-(

 

[lespaul]

Do you really have fields in your table named Infoneed1, Infoneed2, etc? That's a sign your table isn't normalized.

About your questions though, I'm not real clear on what you're trying to do.....you say:

I've created a form based off a couple of queries so that I could display one thing and store another. Example: Pulling in invoice information I store the recordId and Display Inv# Custname and Businessname.

Do you not know how to JOIN one table to another and get all the data in a single query? If I have an Orders table that contains a field CustomerID and a Customer table that contains the business name, the I just JOIN those two tables together on the CustomerID field and I can get any data from Customer that I need based on the OrderID.

SELECT OrderID, Orders.CustomerID, Customer.BusinessName FROM [b]Orders[/b]
INNER JOIN [b]Customer[/b] ON Orders.CustomerID = Customer.CustomerID
WHERE OrderID = 1234

This query will return:

OrderID CustomerID BusinessName
1234 654 Joe Blow's Shop

Now in a single query I have all the data I need for the form.

Now when you say:

[tt] Shows Want
Infoneed1 1 Need Original Signature
Infoneed2 2 Need Supervisors Signature
[/tt]

Do you have a table that lists:
[tt]
NeedID NeedText
1 Need Original Signature
2 Need Supervisors Signature[/tt]

If so, then use the JOIN technique listed above to JOIN this table to your data table and you'll get the information you need.

I'm not sure if that's what you need to complete this or not...it might be better if you listed your tables and structure and explain what data you need to go where and from what form it needs to come from.

Additionally, I would recommend the two documents linked below. If you can't change the table structure to a normalized one, you're going to need to know how to use LEFT and RIGHT joins to get the data that you need, and you're going to want to be proficient at SQL because the Query Design Grid doesn't do complex queries well.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[cous261]

Do you really have fields in your table named Infoneed1, Infoneed2, etc? That's a sign your table isn't normalized.

Yea, I wonder if I was doing the right thing by doing that.

About your questions though, I'm not real clear on what you're trying to do.....you say:

Quote:
I've created a form based off a couple of queries so that I could display one thing and store another. Example: Pulling in invoice information I store the recordId and Display Inv# Custname and Businessname.

What happens here is I use the combo box to display the info in the combo box column(0) while storing column(1) in the table. Of course I use other combo columns for other two field in form as well.
Combo box list
Invoice#(displayed on form), Recid(stored), Custnam, Busnam, Etc.

Do you not know how to JOIN one table to another and get all the data in a single query? If I have an Orders table that contains a field CustomerID and a Customer table that contains the business name, the I just JOIN those two tables together on the CustomerID field and I can get any data from Customer that I need based on the OrderID.

This Data is in one table already as it is a import from mainframe data. All I'm doing is getting what I need in the form to be able to print the form as a cover sheet to return paper work and create a report in the end to show boss why there is delays in getting the paperwork completed. Point the finger on the who the problme so to speak. The invoice data is just a drop down to keep them form having to type it into the from for the next half which is the problem child. NOTE: This data from mainframe is very complex and the invoice number can be duplicated and why I assign recID to this table to id that one record. So in looking at the combo box you could see that invoice twice but it will only have one recID. This is a quick and dirty table that I have no control over without major,major table brake down. All boss wants is to see where the paper flow issues are and create a return sheet(myform) to auditors to get paperwork need to complete payment process.

If I could figure out how to send you a scaled down MDB you could see what I was referring to.

CODE
SELECT OrderID, Orders.CustomerID, Customer.BusinessName FROM Orders
INNER JOIN Customer ON Orders.CustomerID = Customer.CustomerID
WHERE OrderID = 1234

This query will return:

OrderID CustomerID BusinessName
1234 654 Joe Blow's Shop

Now in a single query I have all the data I need for the form.


Now when you say:

Quote:
Shows Want
Infoneed1 1 Need Original Signature
Infoneed2 2 Need Supervisors Signature


Do you have a table that lists: YES

Actual table layout(errorcode is used for the code can change based on user preferences)

Rec# Errorcode ErrorDescription
1 1 Need Original Signature
2 2 Need Supervisors Signature

Here's where I have the 4 combo boxes on form to that use this list to create Infoneed1, 2, Etc.

Again it show Description in form while storing errorcode in Infoneed1. Same for other three combos on form Infoneed 2 inforneed3 etc.

So with Invoice table data here's what you get in table

Rec# InvRec# Infoneed1 infoneed2 Etc. Rdate cdate notes
1 9991 1 2 date
2 9994 3 7 date
3 9999 1 4 date

The input(return) form for this works great it's the completed form is the one with the issue because it's based off the Invoice table above and need to have the cdate and notes field available to update to complete process of paperwork. I'm sure there's something I'm overlooking and from what I see I got the brain to help me narrow this down just wish I could get you the MDB and I'm sure you could put you finger right on it as I've got a lot going on as mother has cancer and having to help her as well as trying to work and handle my lost from two year ago when I lost my wife then dad the next year. So you might can see my brain is in overload these days and working with crazy unorganized data that I can control isn't helping things. So your help will surely be a blessing. SQL side is part of my new studies and probably a hender as well because I can do more with it if i can get it under my belt as I have been using it in other MDB but it data I built and not mainframe garabage like this.

Thanks for your help and I'll be checking back after I get home as it's time to head there.



I'm not sure if that's what you need to complete this or not...it might be better if you listed your tables and structure and explain what data you need to go where and from what form it needs to come from.

Additionally, I would recommend the two documents linked below. If you can't change the table structure to a normalized one, you're going to need to know how to use LEFT and RIGHT joins to get the data that you need, and you're going to want to be proficient at SQL because the Query Design Grid doesn't do complex queries well