Using a combo box to determine which field to search.

What I want to do, hopefully, will be simple enough.  I want to have a search
form that has two fields, one a combo box and the other an unbound field.  I
want the combo box to list the different fields within a table that I want to
search.  I want the unbound field to be where I enter the criteria for
searching the field that I selected in the combo box.  In the past I usually
just created an unbound form and created unbound fields for each field and
then call the fields to the query.  This has always worked well enough but
thought It would be easier using 2 fields as opposed to many.  Any help would
be greatly appreciated.

-- 
Message posted via http://www.accessmonster.com

0
vander
6/7/2010 7:38:27 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
2124 Views

Similar Articles

[PageSpeed] 54

Try this --
Your table having Field1, Field2, Field3 and Field4.
Combo having two fields -- 
1 Field1
2 Field2
3 Field3
4 Field4

Search_Field:  IIF([Forms]![YourForm][Cbo1] = 1, Field1, 
IIF([Forms]![YourForm][Cbo1] = 2, Field2, IIF([Forms]![YourForm][Cbo1] = 3, 
Field3, Field4)))

Criteria:  [Forms]![YourForm][Text1] 


-- 
Build a little, test a little.


"vander via AccessMonster.com" wrote:

> What I want to do, hopefully, will be simple enough.  I want to have a search
> form that has two fields, one a combo box and the other an unbound field.  I
> want the combo box to list the different fields within a table that I want to
> search.  I want the unbound field to be where I enter the criteria for
> searching the field that I selected in the combo box.  In the past I usually
> just created an unbound form and created unbound fields for each field and
> then call the fields to the query.  This has always worked well enough but
> thought It would be easier using 2 fields as opposed to many.  Any help would
> be greatly appreciated.
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
6/7/2010 9:38:02 PM
Thanks Karl,  I appreciate the help, but one question. How do I set up the
combo box so that it lists field names as opposed to records.

KARL DEWEY wrote:
>Try this --
>Your table having Field1, Field2, Field3 and Field4.
>Combo having two fields -- 
>1 Field1
>2 Field2
>3 Field3
>4 Field4
>
>Search_Field:  IIF([Forms]![YourForm][Cbo1] = 1, Field1, 
>IIF([Forms]![YourForm][Cbo1] = 2, Field2, IIF([Forms]![YourForm][Cbo1] = 3, 
>Field3, Field4)))
>
>Criteria:  [Forms]![YourForm][Text1] 
>
>> What I want to do, hopefully, will be simple enough.  I want to have a search
>> form that has two fields, one a combo box and the other an unbound field.  I
>[quoted text clipped - 5 lines]
>> thought It would be easier using 2 fields as opposed to many.  Any help would
>> be greatly appreciated.

-- 
Message posted via http://www.accessmonster.com

0
vander
6/7/2010 10:08:12 PM
vander wrote:
>What I want to do, hopefully, will be simple enough.  I want to have a search
>form that has two fields, one a combo box and the other an unbound field.  I
>want the combo box to list the different fields within a table that I want to
>search.  I want the unbound field to be where I enter the criteria for
>searching the field that I selected in the combo box.  In the past I usually
>just created an unbound form and created unbound fields for each field and
>then call the fields to the query.  This has always worked well enough but
>thought It would be easier using 2 fields as opposed to many.  Any help would
>be greatly appreciated.

You would have to build the filter on the fly... If you open it as a query,
you'd have to use a temporary or stored query that whose SQL property you
could overwrite.  The rest is pretty easy....

control source for first combobox:
SELECT Name FROM MSysObjects WHERE Type = 1 AND Name Not Like "MSys*";

control source for second combobox would be a bit more fun...

Private Sub Combo0_AfterUpdate()
    PopulateSecondComboWithFieldNames Me.Combo0
End Sub


Private Sub PopulateSecondComboWithFieldNames(ByVal strTable As String)
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim lngIndex As Long
    
    Set tdf = DBEngine(0)(0).TableDefs(strTable)
    For Each fld In tdf.Fields
        Me.Combo2.AddItem fld.name, Index:=lngIndex
        lngIndex = lngIndex + 1
    Next fld
End Sub

then you'd have to use these controls to build your SQL statement, and then
you'd probably need to assign the result of that to a holder query's SQL
property... eg

DBEngine(0)(0).QueryDefs("HolderQuery").SQL = <your function to build query>

-- 
Message posted via http://www.accessmonster.com

0
PieterLinden
6/7/2010 10:32:33 PM
Reply:

Similar Artilces:

Edit or Create a Partylist Lookup field
I have an issue - we have Cytrack running between our AVAYA phones and CRM 4.0 and I have enable the option that when a call comes through it opens a Phone Call activity and it automatically connects to the contact for the person calling in. Great except while we have all cleitns in as contacts - we also have clients either in as a Lead or an Opportunity when we are doign current business and I want to change the phone call activity recipient. I can do this but it comes up a "Look Up Records" screen and you havea drop down list to pick what entity you wish to locate the cl...

Instant Search, Advanced Find, Search Folders, and Windows Desktop
Instant search stopped working for me. Now if I search for even the letter "a", I get the following message: No matches found for: "a". When I ask "advanced find" to find any emails with categories contains TNE01, it returns no results. However, advanced find works somewhat. For example when I look for items in my inbox with "a" in the subject line, it returns 78 matches. When I use "search folders", to find emails with categories = TNE01, 14 items are returned. When I use "search folders" to find items with "WC" in freq...

Am I using the pop3 connector properly?
I currently host mail for udc.net on a machine running mailsite. I want to convert everyone over to exchange, but cannot do everyone at once. I believe that I need to use the pop3 connector. All MX records point to the current box running Mailsite. In exchange, I have created a pop3 connector for my own mailbox, and added the SMTP domain and email address for udc.net (so when I send mail, it will show me@udc.net as the from, and return address) I connector definately downloads mail from the pop3 mailbox. But it gets lost. The messages do not appear in my exchange mailbox. If I remove t...

using a UDF in place of a cond. format, b/c I need 4, not three
I know this is an age old topic here, and I'm really hoping that I don't just get referred to another web page [that type of help of course has its place and merits]. And if I had the time, I would have more fun going through docs and notes to find out how to do it, but time is not something I can barter. What I'm looking at is: I'm thinking along the lines of an example where I normally set the borders of my columns [except if it's the first row] in a cond. format via something like this: =AND((NOT(ISBLANK($A1))),(ROW()>1)) But, if I need that space for a Con...

Using Rules to Respond to Messages
I'm setting up resource scheduling and like some others have noticed, if a user submits a calendar item to a resource but puts the resource in the required or optional field the calendar has to accept or decline the meeting. Well, your resource is setup to automatically accept resource scheduling and no one is going to read the requests submitted as email. I was thinking I could setup a rule that automatically resonds to the user to tell them that they have selected a resource as a person. Please resubmit this request and put the resource in the resource field. PROBLEM: I can'...

PDFs do not display in OWA using FE/BE scenario, IE 6, Adobe Reader 7.0.7
Greetings, My organization is currently running Microsoft Exchange Server 2003 SP2 in a FE/BE scenario with the FE serving OWA. We are experiencing problems with our OWA users not being able to open PDF attachments within OWA when they are using IE 6 (fully patched) and Adobe Reader 7.0.7. After some testing, I have discovered the following: When using: Abobe Reader 7.0.7 and IE 6 (fully patched) - PDFs will not display in OWA (no errors given) Adobe Reader 7.0.7 and IE 7 Beta 2 - PDFs will display properly Adobe Reader 7.0.7 and Firefox (fully patched) - PDFs will display properly ...

Access text box
I was trying to add text boxes to my database in form design view, which worked fine up until the moment when i was back in form or table view. For some reason when I add detail in one of the cells of the new column it was automatically copied to every other cell in that new column. This has only been happening with the new columns and the ones I created using the wizard when starting the database seem to be working fine. What can I do about this? On 25 mrt, 12:19, FernP <Fe...@discussions.microsoft.com> wrote: > I was trying to add text boxes to my database in form desig...

Preventing writing in a combo box field
Ok this is my problem: I've a combo box with several employee names. Of course that when the employee is chosing it's name it writes the first letter so that the combo box filters the names to the closest one. Access then higlightes the employee name execpt the letter he wrote. Employee then hits enter and Access displays its full name, has in the combo box options, neverthless I have a hidden form that copies employee name to feed other forms. The problem is that if employee chooses is name has discribed, Access will only select all the other letters of his name execpt the one ...

RUS does not allow mail boxes to be opened
When creating new user/mail box in AD on our Exchange 2000 server, the RUS generates a smtp address, but when user logs on Outlook reports 'Unable to open your default e-mail folders, you do not have permission to logon'. We have checked ADSI and the setting 'msExchUsserAccountControl' has a value of 'not set'. If you change this to '0', then the mailbox can be opened. Why is RUS not setting this value? We have tried creating new RUS and recepient policy and this does not make any difference. The user has permission to open his own mailbox. We have tried ...

Using Automation to Send HTML...
The following code used to work fine in Outlook 2000, now with Outlook 2002 it doesn't. Now it leaves a BLANK body. Can anyone tell me why this no longer works? Also, we try the same thing with an RTF type file as the body (.body property), and it just puts junk in the body and does not interpret it as a RTF file. Thanks. Bob ***************************************************** oOutlook = CREATEOBJECT("outlook.application") oMail = oOutLook.CreateItem(0) WITH m.oMail m.oRecipient = .Recipients.Add("test@test.com") m.oRecipient.type = 1 .Subject = &quo...

Moving mailboxes using Active Directory Users and Computers
This is a multi-part message in MIME format. ------=_NextPart_000_0022_01C61DC8.6FAD4950 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Question: When moving mailboxes using AD Users and Computers from = Exchange server A to Exchange Server B, what will be the effect on users = that currently have their mailbox open in Microsoft Outlook? Some of the = users have Outlook 2003 and some of them have Outlook 2002. Is it best = to do the move when noone is on? but the thing is in this organization, = we have so laptop users that may not be on...

Apostrophe in search string
I have a simple SQL string that is run from a form when a field is updated: strSQL = "UPDATE tblDesign " & _ "SET " & strFieldNameForUpdate & " = '" & strDM & "' " & _ "WHERE DesignName = '" & strName & "' ;" It works fine except for where there is (rarely) an apostrophe in the strName field data. I cannot work out how many extra quotes to put into the sql build string to get round this. A debug.print of the string that causes a syntax problem is: UPDATE tblDesign S...

Link to other slides from table using c#
Hi, I'm looking to create links from a certain slide to another, and I need those links to be from within a table. I need it to be something like this: var pptApp = new PowerPoint.Application(); var presentation = pptApp.Presentations.Add(Office.MsoTriState.msoFalse); var slides = presentation.Slides; slides.InsertFromFile(@"C:\Users\Tng1\Documents\PresentationTemplate.potx" , 0, 1); var titleSlide = slides[1]; titleSlide.Name="TitleSlide" ; var tableSlide = slides[2]; tableSlide.Name = "TableSlide" ; va...

What's the best way to diagram a supply chain using Visio?
What's the best way to diagram a supply chain using Visio? ...

covering part of a text box border
I want to have a large text box with a border...I then want to place a smaller text box within the larger one (with its own border). However, some of the border of the large text box is vissible within the small text box. Is there a way to set the small text box on top of the larger one and partially cover the large text box border? I can not find an answer to this problem after many searches. set the smaller box on a higher layer and set a fill color on the = smaller box. "Willie Birdie" <Willie Birdie@discussions.microsoft.com> wrote in = message news:95B71C45-E4BD-...

Sendinf personalised mails to a group using Outlook
Hi All, I would like to know how to send personalised mails to a group (keeping the groups in bcc) using outlook 2003/2007. Please share your thoughts. regards, Jaseel You have stated contradictory points. BCC does not "personalise" it just hides the recipient list. If you want to personalise, ie use data fields like first_name and last_name, you need to do a mailmerge to email. There are many posts here on how to do that. Regards Judy Gleeson MVP Outlook in Canberra, Australia "Jassy" <Jassy@discussions.microsoft.com> wrote in mes...

Sort Using Different Columns
I am trying to compile a list of sales by customer and total purchases by that customer in a series of months. I have column a as a master list of customer names. Column B is going to be the charges for that customer in lets say may. Column c is going to be the charges for that customer in June. The problem I am having is that not all customers have charged from us in the same months so when I bring my data into this worksheet it contains the customer name in one column and the charges in another. Doing this does not allow the customer names for one month to line up with the next m...

removing locked cost field from product form
On the Product entity form the 3 fields 'list price' 'standard cost' and 'current cost' are locked. Is there a way to get the 'current cost' field off the form so it can't be seen by users? thanks Garnet Hi Garnet, You can use the following Javascript Onload of the form: crmForm.all.<<name of the field>>_d.style.display = "none"; HTH, Niths "Garnet" wrote: > On the Product entity form the 3 fields 'list price' 'standard cost' and > 'current cost' are locked. Is there a way to get the &#...

Entering vacation into a recurring batch using HR attendance
Hello, I have a client who maintains recurring batches and keeps track of vacation and sick payouts that way. We recently moved vacation and sick from payroll to HR. Now there is a restriction that does not allow entering these linked vacation/sick pay codes in a recurring batch. Is there around this or is it basic functionality of attendance? -- Greg. It's the way it is. You'd probably be better off with PTO Manager unless you need more than vacation and sick time. If you do, you'd want to look at Enterprise Leave Manager from Integrity. -- Charles Allen, MVP &quo...

lookup link for customer address on account, case, address search
The way the address associations are handled today create tremendous gaps in the structure of CRM records compared to the structure of integrated backoffice financial systems records. What should be trivial integration tasks lead to a LOT of workarounds and customers frustration. Here is how we can improve. Abandon the idea of hardcoding first 2 address records on the account form. Add 2 address lookup fields on the account (relationships) that could be used to select any of the account addresses and expose them on the account form (still show individual address fields on the accoun...

Access exchange using IMAP
Hi There. I cannot access my email message using IMAP to exchange 2003. The statement as below: Connection refused Configuration: Account: Staffmail Server: 172.18.3.203 User name: Staff\Taufik Protocol: IMAP Port: 143 Secure(SSL): 0 Code: 800cccd9 any ide how so solve it? thanks Have you started the IMAP service and made sure that IMAP access is enabled for your user account? Exchange 2003 disables IMAP and POP3 services by default. -- Ben Winzenz Exchange MVP MessageOne "Muhamad Taufik Khasim" <Taufik@uniten.edu.my> wrote in message news...

Using "OR" in an IF statement
I am trying to use the command OR in an If statement and I am having no luck. In Lotus 123 I could string commands by using #OR# to produce a common result. For example: =IF(J3="INVEST" #OR# IF(K3 = "INVEST",1,"NO")) I can't seem to make this work in Excel. Can anyone provide me with a correction? Doug, Like this =IF(OR(J3="Invest",K3="Invest"),"The true condition","the false condition") If the true or false condition is numeric then drop the quotes -- Mike When competing hypotheses are other...

Using the 'S' key
PowerPoint 2003, Win XP. A presentation with music running from start to end, using the 'S' key to pause the presentation also stops the music. On pressing again to continue the presentation, the music does not continue. I'm not hopeful, but is there a way to continue the music, even if it means that it starts from the beginning? I have the sound set to 'loop until next sound' on the second slide, the first being an introduction slide. Thanks. Brian. On most systems embedded and linked sound act differently in this respect. If you LINK to the so...

Use a range name in VB with IF Then Else statement
Thanks for looking at this one..... when writing a macro in visual basic, how would you write code to do something like this: IF "Named Range 1" is not equal to "Named Range 2" THEN (do a simple cut/copy/paste which I've allready figured out) ELSE END IF I can do this with range as cell address ("A2") but don,t know how to reference the Named Range. Thanks.... Spydor -- spydor ------------------------------------------------------------------------ spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28438 View this t...

Using VBA
Hello, I have a summary timetable sheet, and each customer has a differen colour. If someone is due to visit them on day x, then the cell i shaded in that particular customer. I then have an individual sheet for each customer and I want to be abl to copy the formatting from the summary to the individual custome sheet without having to cut and paste for each individual customer. This is what I have so far Dim Cell As Object Dim CurrentCell As Object Range("B10:Y200").Select For Each Cell In Selection If Cell.Interior.ColorIndex = 52 Then Cell = CurrentCell Sheets("Cus...