Multiple listings

I don't really know if this the right form or how to describe what 
information I'm looking for... but I'll try this first:

I have a tables for the folowing items: product types, product layers, 
application methods, manufacturer names, a table named after each 
manufacturer and a table with fields for manufacturer name, product type, 
application method and product name. I ended up with all these tables because 
I do not know how to filter out duplicate items from a query. For example: 
there are 4 ways to apply "Item3". Like so...

Item1 - Apply1
Item1 - Apply2
Item2 - Apply1
Item3 - Apply1
Item3 - Apply2
Item3 - Apply3
Item3 - Apply4

However, there may be 12 different application methods and this particular 
manufacturer only carries products that deal with certain methods. Clear as 
mud? :) I have a query string in Excel that fetches this information and 
populates a two different comboboxes in a userform. Like I said before, the 
trouble is, it populates the comboboxes with EVERYTHING from the table. I 
only want it to populate the comboboxes with certain information. When I tell 
Excel what manufacturer to look up I only want the item list and its 
application method (ie - Item1, Item2, Item3 - Apply1, Apply2, Apply4, 
Apply5, Apply9) not a repeat of an Item and it's alternate application method.

As of right now, Combo2 from my userform in Excel is populated from a 
mftrNames table like this:

'==== Excel Code ====
Private Sub tasklist_Change()
Dim db As Database
Dim rs1 As Recordset
Dim sysMftr As String
mftrlist.Clear
mftrlist.Text = "Select..."
Set db = OpenDatabase("\\myServer\Users\SQL\roofing.mdb")
sysMftr = "SELECT * FROM mftrNames;"
Set rs1 = db.OpenRecordset(sysMftr)
With rs1
    If Not .BOF Then .MoveFirst
        While Not .EOF
            With mftrlist
                .AddItem rs1("mftrName")
            End With
        .MoveNext
        Wend
End With
Set rs1 = Nothing
db.Close
Set db = Nothing
End Sub

Which Works great. So my Manufacturer Combo is now populated. When I choose 
a manufacturer, the query calls "SELECT * FROM " & mftrlist.Text & ";" which 
_works_ but duplicates entries from the named manufacturer database... So I 
guess that's the question - how do I get my query to move on to the next 
record or field without repeating/missing data.
0
Utf
4/18/2007 10:10:01 PM
access 16762 articles. 3 followers. Follow

1 Replies
614 Views

Similar Articles

[PageSpeed] 1

Nevermind.

I figured it out.

"IT_roofer" wrote:

> I don't really know if this the right form or how to describe what 
> information I'm looking for... but I'll try this first:
> 
> I have a tables for the folowing items: product types, product layers, 
> application methods, manufacturer names, a table named after each 
> manufacturer and a table with fields for manufacturer name, product type, 
> application method and product name. I ended up with all these tables because 
> I do not know how to filter out duplicate items from a query. For example: 
> there are 4 ways to apply "Item3". Like so...
> 
> Item1 - Apply1
> Item1 - Apply2
> Item2 - Apply1
> Item3 - Apply1
> Item3 - Apply2
> Item3 - Apply3
> Item3 - Apply4
> 
> However, there may be 12 different application methods and this particular 
> manufacturer only carries products that deal with certain methods. Clear as 
> mud? :) I have a query string in Excel that fetches this information and 
> populates a two different comboboxes in a userform. Like I said before, the 
> trouble is, it populates the comboboxes with EVERYTHING from the table. I 
> only want it to populate the comboboxes with certain information. When I tell 
> Excel what manufacturer to look up I only want the item list and its 
> application method (ie - Item1, Item2, Item3 - Apply1, Apply2, Apply4, 
> Apply5, Apply9) not a repeat of an Item and it's alternate application method.
> 
> As of right now, Combo2 from my userform in Excel is populated from a 
> mftrNames table like this:
> 
> '==== Excel Code ====
> Private Sub tasklist_Change()
> Dim db As Database
> Dim rs1 As Recordset
> Dim sysMftr As String
> mftrlist.Clear
> mftrlist.Text = "Select..."
> Set db = OpenDatabase("\\myServer\Users\SQL\roofing.mdb")
> sysMftr = "SELECT * FROM mftrNames;"
> Set rs1 = db.OpenRecordset(sysMftr)
> With rs1
>     If Not .BOF Then .MoveFirst
>         While Not .EOF
>             With mftrlist
>                 .AddItem rs1("mftrName")
>             End With
>         .MoveNext
>         Wend
> End With
> Set rs1 = Nothing
> db.Close
> Set db = Nothing
> End Sub
> 
> Which Works great. So my Manufacturer Combo is now populated. When I choose 
> a manufacturer, the query calls "SELECT * FROM " & mftrlist.Text & ";" which 
> _works_ but duplicates entries from the named manufacturer database... So I 
> guess that's the question - how do I get my query to move on to the next 
> record or field without repeating/missing data.
0
Utf
4/18/2007 11:00:02 PM
Reply:

Similar Artilces:

Sending E-Mail using Outlook with multiple attatchments in C#
I am a newbie in C# but have experience in other OO languages. I want to write a COM component in C# that I can reuse in a application that we are developing in Powerbuilder. The COM should have the ability to: 1) Open a Outlook window. When the user enters any e-mails, the outlook should validate for the right adress using the company directory. (I guess We dont have to code for this. Outlook can do this) 2)The Outlook window should be populated with attatchements of all the files from a directory. The directory will be specified by the user @ runtime. 3) The user will inout the...

country list
Hi there, I am trying to create a data validation list containing all countries. How can I get such a list into Excel without actually having to type every country? Thanks in advance http://dir.yahoo.com/Regional/Countries/ -- HTH Bob Phillips (remove nothere from email address if mailing direct) "BigBloke" <BigBloke@discussions.microsoft.com> wrote in message news:7028A1D2-F2D2-4BB7-A5AD-14244A9CAC33@microsoft.com... > Hi there, > > I am trying to create a data validation list containing all countries. How > can I get such a list into Excel without actua...

Is it possible to create Drop Down Lists?
Is this feasible in excel? Or is it just easier to add comments if yo want to provide choices of inputs -- Message posted from http://www.ExcelForum.com Hi this is quite easy. Have a look at 'Data - Validation - List'. Also see: http://www.contextures.com/xlDataVal01.html -- Regards Frank Kabel Frankfurt, Germany > Is this feasible in excel? Or is it just easier to add comments if you > want to provide choices of inputs? > > > --- > Message posted from http://www.ExcelForum.com/ Shadestreet, read about drop-down lists in Debra Dalgleish's discussion of Dat...

Repeater and XML Node List
Alrighty, so if I have an XmlNodeList as the data source for a repeater, in my repeater, I would like to do things like: <%# DataBinder.Eval(Container.DataItem, "ChildNodes[0].InnerText") %> But everytime I do it says stuff like: 'System.Xml.XmlChildNodes' does not allow indexed access. Of course, it's perfectly happy to use FirstChild.NextSibling.InnerText, but I'd like to use indexed methods or properties, but it does not seem to work. Here is sample code: <%@ Page Language="C#" autoEventWireup="false"%> <%@ Import Namespa...

list box sort
I have a list box that uses a query for the rowsource. How would I change the sort order (on click of command button): I thought of Dim strSQL as string strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _ " ORDER BY NameLFMN" And then assign strSQL as the new query def, but that get's into removing the ";" etc. Is there a simpler way? Thanks for your time!! Sam Copy the SQL statement from pqSearchList into your code, and chop of the ORDER BY clause. Define a string to set up with the ORDER BY clause. Assign the result to the list bo...

saving multiple pages
I used a template to create a 4 page newsletter. When I save it as a jpeg the full 4 pages are not saved but only one page does. How can I save the document as a full 4 pages and as a jpeg? I want to e-mail this newsletters so that it can easily be accessed by mu customers. Any help appreciated About the only way would be to create a new Publisher document, in page setup create a custom size 44", copy/paste your newsletter pages and save as a .jpg. The best solution is to send your newsletter as a PDF. If you have Publisher 2007 there is a PDF add-in from Microsoft https://www.m...

Outlook 2003 / Cached Mode / Global Address List
Is there any way of getting the GAL to update quicker when using Cached mode? We are adding a lot of users over the next few days and it would be good if they could see the new email users as soon as possible. Change the schedule for how often your Offline Address Book is built - do this in Exchange System Manager. I think the default is once per day at something like 4am or 5am. There is also a registry setting to force Outlook 2003 to use a GC, but that's a bit OTT for what you want to do. -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchang...

How to create a formula to remove multiple . in a numbers?
Ex: 11.2101.1030.0000.0000.1982 how can I remove these dots in multiple numbers on a spreadsheet? If you really mean a formula, meaning you will retain the dotted values and display the number without the dots in another cell, then use this formula... =SUBSTITUTE(A1,".","") If, on the other hand, you are trying to modify each cell's value in place, then you can use Edit/Replace from Excel's menu bar... put a dot in the "Find what" field and leave the "Replace with" field blank (empty). -- Rick (MVP - Excel) "Che...

Multiple Background Color schemes in Great Plains
It would be helpful to be able to set a different background color scheme for diffeernt companies within Great Plains versus having the system wide color scheme that is currently used (Version 8.0). This would allow the "visual" cue for data entry and testing so the user will have an additional signal as to which company they are working in and not rely just the notation up on the tool bar. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button i...

Getting the correct and secure time for multiple users.
I am creating a program with access for employees to clock in and out when working overtime. I have a form that will record the current time using the "Now()" function, and checks for the certain time you are allowed to clock in and it works great. However, the time recorded is the idividual machine time, to if somebody wanted to they could change their computers time to punch in a few minutes early, and then again to punch out late. Is there way to get a time off of our server (we do not have an SQL server) and use that for all of the users? ...

Sending email from multiple users from Outlook 2007 Vista
We have 2 email accounts and can receive them to each account but CANNOT send from each account. All messages from both users go out under one user's name. Is there a fix for this? Help!! How is Outlook 2007 accessing the e-mail accounts? Is it connected to an Exchange server, POP3, IMAPv4, etc... <chip@kingwoodcable.net> wrote in message news:1185485984.108736.258740@l70g2000hse.googlegroups.com... > We have 2 email accounts and can receive them to each account but > CANNOT send from each account. All messages from both users go out > under one user's n...

number list compare
How do a compare a number to a list of numbers to get the next highest and next lowest number? In article <6DBB1D07-4ABE-4885-8DAE-5552AC7603C6@microsoft.com>, "jpmmschi" <jpmmschi@discussions.microsoft.com> wrote: > How do a compare a number to a list of numbers to get the next highest and > next lowest number? Assuming that your list of numbers are in Column A... Next lowest number: =MAX(IF(A1:A10<B1,A1:A10)) Next highest number: =MIN(IF(A1:A10>B1,A1:A10)) ....where B1 contains the number being compared to. Both these formulas need to be entered...

Multiple sheet references in one function
=SUM(--('Sheet1'!$B$2:$B$2000<=BB$3),--('Sheet1'!$B$2:$B$2000>=BB$2),--('Sheet1'!$D$2:$D$2000=$B7), --('Sheet2'!$B$2:$B$2000<=BB$3),--('Sheet2'!$B$2:$B$2000>=BB$2),--('Sheet2'!$D$2:$D$2000=$B7), --('Sheet3'!$B$2:$B$2000<=BB$3),--('Sheet3'!$B$2:$B$2000>=BB$2),--('Sheet3'!$D$2:$D$2000=$B7), --('Sheet4'!$B$2:$B$2000<=BB$3),--('Sheet4'!$B$2:$B$2000>=BB$2),--('Sheet4'!$D$2:$D$2000=$B7)) Sheets 1-4 all list claims but they need to be seperated. The values that are target...

multiple email addresses in one identity (OUTLOOK EXPRESS)
HELP!!! can anyone tell me how in the world i can set up multiple e-mail addresses in OUTLOOK EXPRESS under the same identity but give each e-mail address its own inbox? i have like 5 email addresses and it is irritating to have all five receive emails through the same inbox. i would much rather be able to differentiate which email is from what account without so much trouble! currently, one of my email addresses is a hotmail account, so that receives its own inbox (typical that microsoft would make its own products stand out). and if i add even more hotmail accounts, each one of tho...

copy and pasting a find all list into another column
Does anyone know how I can paste the find all into a new column in the same corasponding row number? I did a copy from the "Find>find all" list by right clicking the list then do "copy". Those cells (non-adjacent cells) get highlighted but when I past it in the next column to the same row it pastes the list in order not in the correct cell. Must I use formulas to do this and if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP! thanks Maybe you could use a formula instead of the edit|Find. Say your data is in Columns A:E and you were looking for ...

Compare Names in a column and create single list with adjacent vla
Good Morning, Here's the challenge the information shown below is on a single worksheet. What I need to do is create a single column of names from Columns A,C,E,G. The adjacent columns B, D, F, H contains values associated with each name and need to be added next to the proper name in the new list...Below I added what is looks like currently and how it need to look after run the macro or VB script. Col A Data1 Col C Data 2 Col E Data 3 Col G Data 4 Name1 0.3 Name1 100% Name 1 15 Name1 0.1 Name2 0.5 N...

Vlookup with Multiple Criteria
I have 2 spreadsheets that both contain store numbers and the accounts they belong to. I want to compare the two files to make sure that all of the store numbers in one (smaller) list are included in the other (master) list. I usually use the VLOOKUP formula to search for the store numbers in the small file and return the same values in the master file. In this instance, some of my store numbers are duplicated across accounts. For example, ABC retailer and XYZ retailer might both have a store #123. I want to do the same thing I have been doing with the previous VLOOKUPS, but restrict ...

How do I print multiple files from windows explorer?
I can't print multiple files from explorer. I select them, then hit print, but only a few will print out, maybe 3 out of 10. Also, if I need to set the properties of the files to something different, like landscape on 11x17" paper, how do I do that for all the files without having to open each one. Thank you! >>Also, if I need to set the properties of the files to something >>different... As far as I know you have to open a workbook to change its printer page setup. There is no way to override the workbook settings externally. -- Jim "GMed" <GMed@d...

Email print style defaults to table when printing multiple items from IMAP folde
When printing multiple email messages from an IMAP account using Outlook 2000 or XP, the print style seems to switch from memo to table which prints the headings of every message in the folder instead of the contents of the marked messages. This only happens using an IMAP account. It doesn't appear to happen using a POP3 account. Does anyone know how to change this default setting or how to fix this? Thanks, Richard E. Maier Community Legal Services Inc. I.T. Manager ...

Can I freeze multiple rows?
I'm working on a spreadsheet that has contact information in the farthest lefthand collumn and collumn headers at the top of the spreadsheet. Is there a way that I can freeze both of them so that I always know what I'm looking at without having to scroll all the way up or all the way over? Thanks a bunch! =) If you click on cell B2 and do Window > FreezePanes, then both column A and row 1 will be "frozen"....... Vaya con Dios, Chuck, CABGx3 " <littlestar@discussions.microsoft.com> wrote in message news:3B58B895-7C24-4418-87B3-B3B5F35AA682@microsoft.com....

Can a SUMPRODUCT array formula be used across multiple worksheets?
Hi - I have a workbook with 32 worksheets (Summary and 1-31) ... all sheets are formatted the same way, with names in column A and then data- values in B:L. The list of names on the Summary Sheet is all- inclusive; however, these names may or may not appear on sheets 1-31. Of course, the Summary should add up the values (per name) on sheets 1-31, and handle the fact that a) the name may not exist on a specific sheet, and b) that the value on the sheet may be an error (#N/A). Here's what I have so far, which doesn't work: =SUM(IF(ISNUMBER(VLOOKUP($A28,'1:31'!$A$2...

list of installed objects
Hello, Where sould I look for information related with Instealled Ole Controls on the PC? I need to get a list of all objects registered in the system. Thanks a lot, Bastian ...

Macro to shorten a list
Has anyone got a macro that will find duplicate values/names on a sheet and copy just one of those values onto a seperate sheet within the same workbook. Mark your range of data Data>Filter>Advanced Filter> select Unique values and Copy to Another location and make Copy to Sheet2!A1 Switch on the macro recorder as you carry out this task then invoke the macro each time you wish to use. -- Regards Roger Govier "JGB" <JGB@discussions.microsoft.com> wrote in message news:13464F20-E246-4DFA-B5F3-BF5636D88747@microsoft.com... > Has anyone got a macro that wi...

Direct Statement Bank List?
Is there a list somewhere of the banks the provide direct statements for MS Money? Take a look at: http://microsoft.com/money/online/FIList.asp?FIList=5 DAE "Derek" <derekb@nospamderekb.com> wrote in message news:O46JQrXUDHA.2420@TK2MSFTNGP10.phx.gbl... > Is there a list somewhere of the banks the provide direct statements for MS > Money? > > ...

Not getting entire global address list, only in outlook 2003
If I open up outlook 2000 for example, my entire globabl list is there. In 2003, I'm getting the wrong list. (We have a different global address list for each of our clients and only administrators should be receiving the full global list. I'm recieving one of the client lists instead.) How does Outlook 2003 determine which global list its grabbing? ...