Forms / Combo box

[Anonymous1986]

Hello all


My first post, I hope I make sense!


I have a form that lists companies associated with a job. I have created a combo box that I want to list all contacts associated to each company that can be selected by the user. Currently my combo box only shows one contact in the dropdown at a time so where a company has three contacts, for example, that company is listed three times separately on the form when i only want it to show once. Tried so many options i can't even remember! When I've tried a list box it doesn't even show any contacts. What am i doing wrong!?


Thanks!

 

[Minty]

In the combo box properties , what is the rowsource set to?
If it's a SQL query can you copy and paste it here ?

If not You might need to post your DB here (Compact and repair then ZIP it) for someone to have a look.

 

[Anonymous1986]

Thanks for quick reply!


SQL below


SELECT DISTINCTROW [Supplier Contacts].Contact
FROM [Supplier Contacts];



I realise probably something missing. Company on the form is pulled from a different table to Supplier Contacts.

 

[Anonymous1986]

Sorry what I posted before was from another version! Currently I don't have a row source. I have control source set to Contact (which is the relevant field in the Supplier Contacts table) and Row Source Type as Table/Query.


When I input the above SQL it lists all contacts across all the relevant companies.


Sorry to be confusing!

 

[Minty]

You need to filter the query results to match the company.

Lets make some assumptions;

Your contact list has the CompanyID in it as a foreign key.
Your CompanyID field is in a textbox called txtCompanyID.
Your Form is called frmCompanyInfo

On that basis your ComboQuery should be something like

SELECT DISTINCTROW [Supplier Contacts].Contact
FROM [Supplier Contacts]
WHERE [Supplier Contacts].CompanyID = [Forms].[frmCompanyInfo].[txtCompanyID]

Does this make sense?

 

[Anonymous1986]

I amended the code to


SELECT DISTINCTROW [Supplier Contacts].Contact
FROM [Supplier Contacts]
WHERE [Supplier Contacts].Company)=[Forms].[Subcontractor List].[Company Name]



This makes the dropdown one name which is the same for each company.


The SQL for the form is


SELECT Estimates.EstimateNo, [Junction - Subcon Estimates].Category, [Junction - Subcon Estimates].[Company Name], [Junction - Subcon Estimates].Comments, [Junction - Subcon Estimates].[Return Date], [Junction - Subcon Estimates].Status, [Junction - Subcon Estimates].[Date Sent], [Junction - Subcon Estimates].[Date Returned], [Junction - Subcon Estimates].EstimateName, [Supplier Contacts].Mobile, [Supplier Contacts].DirectDial, [Supplier Contacts].Email, Estimates.TenderReturnDate, Addresses.TelephoneNo, [Supplier Contacts].Contact
FROM ((Subcontractors INNER JOIN Addresses ON Subcontractors.CompanyName = Addresses.[Company Name]) INNER JOIN ([Junction - Subcon Estimates] INNER JOIN Estimates ON [Junction - Subcon Estimates].EstimateNo = Estimates.EstimateNo) ON Subcontractors.CompanyName = [Junction - Subcon Estimates].[Company Name]) INNER JOIN [Supplier Contacts] ON Subcontractors.CompanyName = [Supplier Contacts].Company
WHERE (((Estimates.[Select])=Yes))
ORDER BY [Junction - Subcon Estimates].Category;



Originally the form was set up to show one contact that was defaulted as 'Estimating Contact' but this was not working for the team. So i changed it in the query builder?


Unfortunately I don't think my primary keys / ids / etc are set up in the best way.

 

[Minty]

I think you may need to post up a picture of your tables and the relationships. My SQL reading / interpretation skills are poor on Fridays

Or remove the sensitive data and post up a zipped version of your DB .

 

[Anonymous1986]

I can't post link yet as I'm too new a user! And even zipped it exceeds the limit I'm allowed. Will have a think how I can do this.

 

[Minty]

Make sure you compact and repair before trying to zip.
It can dramatically reduce the db size.

 

[Anonymous1986]

Yeah I have - it's still nearly 10mb when zipped and my limit is 2mb.


Apparently once I've hit 10 posts I can attach link!

 

[Minty]

Strip out all but 20 or 30 records from your tables see if that helps...

Remove any complicated forms that we don't need to see.

 

[Minty]

Be warned that most people won't download from other sites...

 

[Anonymous1986]

Oh ok thanks ... Maybe I can delete some of the forms out of it for this purpose.

 

[Frothingslosh]

Also, note that spam posting just to reach 10 has been known to result in the spam posts getting deleted.

Just post a stripped-down database (just enough to run the part you're stuck on, with fake data) inside a zip file. You can do that regardless of post count.

 

[Anonymous1986]

I'm still at 2.6mb! Literally don't think I can strip it down any further than I have ...

 

[Frothingslosh]

Is it over 2 megs even if you zip it?

 

[Anonymous1986]

Yes even with minimal data in the tables. The tables are the bulk of it but if I don’t keep them my form doesn’t work because of the way we set up the database.

 

[Frothingslosh]

Okay, next question - do you need ALL of the data in every table? All we really need is enough for the application to be functional. Things like invoice systems only needing a couple invoices, etc.

Because, seriously, if just a handful of records results in a 2 MB database, you probably have other issues as well.

Thinking about it, do you have graphics as backgrounds? Any modules you're not using for this specific form? Look into things like that, too. We're not looking for a functional database, just this one form and enough infrastructure for it to work.

 

[Anonymous1986]

I've tried a different approach - does it make a difference where tables have been linked and repasted? The form isn't generating with the data but the structure is there, hopefully this is enough for you to get an idea?

I definitely have many issues! A complete beginner, it's been a trial and error build.

This goes back to version before i've tried to make changes. Essentially what I want to do is replace the contact name field (which combines first name / last name / position) with contact field as a dropdown to select the names available for each company.

Thanks!

 

[essaytee]

I got your form to generate data. The recordsource of the form needs to be changed slightly, image attached. Your table, "Junction - Subcon Estimate" field "EstimateNo" needs to be linked to table "Estimates" field "EstimateName" and not "EstimateNo".

Data will now be generated; this is as far as I got, thought it might be useful for others if looking at this.

Just some observations:
1. No spaces in table names, it creates annoying headaches when coding (Easy to forget to enclose them in [].
2. Company table, what you refer to as Subcontractors, the key field is the ID field, and that would be the primary key, not the company name.
3. In other tables where referring to the company use the Company ID as the data, that is, the foreign key is the Company ID.
4. As with the Company ID (primary/foreign key) method, applying the same method to the Estimate table(s) this mishap above probably would not have occurred.