473,597 Members | 2,459 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Searching on a form using two combo boxes

77 New Member
I am using Microsoft Access 2010.I am trying to perform a search in a form (this form is a continuous form) using two combo boxes: a combo box called ClientID and another combo box called PremiumInvoiceN umber. No result is returned after selecting the two combo boxes. Can you tell me what I am doing wrong? Thank you in advance for your help.

Expand|Select|Wrap|Line Numbers
  1. Function SearchCriterica()
  2. Dim ClientID, PremiumInvoiceNumber As String
  3. Dim task, strCriteria As String
  4.  
  5. If IsNull(Me.CboClientID) Then
  6.     ClientID = "[ClientID] like '*'"
  7. Else
  8.     ClientID = "[ClientID] = " & Me.CboClientID
  9. End If
  10.  
  11. If IsNull(Me.CboPremiumInvoiceNumber) Then
  12.     PremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
  13. Else
  14.     PremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
  15. End If
  16. strCriteria = ClientID & "And" & PremiumInvoiceNumber
  17.     task = "Select * from frmBillingInvoicePremiums where " & strCriteria
  18.     Me.frmInvoicePremiumsSub.Form.RecordSource = task
  19.     Me.frmInvoicePremiumsSub.Form.Requery
  20.  
  21. End Function
Aug 12 '15 #1
8 1831
jforbes
1,107 Recognized Expert Top Contributor
Line 16 should have some spaces around And:
Expand|Select|Wrap|Line Numbers
  1. strCriteria = ClientID & " And " & PremiumInvoiceNumber 
Aug 12 '15 #2
wirejp
77 New Member
Thanks, jforbes, but the no result is returned after selecting the two combo boxes. I have changed the field name from ClientID to ClientName.

Some more background: the subform called frmInvoicePremi umsSub,is set as a continuous form and it is referencing a query called queryBillingsPr emium.

The code is shown below: -

Expand|Select|Wrap|Line Numbers
  1. Function SearchCriterica()
  2. Dim strClientName, strPremiumInvoiceNumber As String
  3. Dim task, strCriteria As String
  4.  
  5. If IsNull(Me.CboClientName) Then
  6.     strClientName = "[ClientName] like '*'"
  7. Else
  8.     strClientName = "[ClientName] = '" & Me.CboClientName & "'"
  9. End If
  10.  
  11. If IsNull(Me.CboPremiumInvoiceNumber) Then
  12.     strPremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
  13. Else
  14.     strPremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
  15. End If
  16. strCriteria = strClientName & " And " & strPremiumInvoiceNumber
  17.     task = "Select * from queryBillingsPremium where " & strCriteria
  18.     Me.frmInvoicePremiumsSub.Form.RecordSource = task
  19.     Me.frmInvoicePremiumsSub.Form.Requery
  20.  
  21. End Function
Aug 12 '15 #3
jforbes
1,107 Recognized Expert Top Contributor
What I do when I get into the situation you are in:
  • Put a breakpoint on line 18. (Debug|Toggle Breakpoint)
  • Run the code and do whatever needs to be done to have Access Break on line 18.
  • Ctrl-G to open up the Immediate Window.
  • Type "?task" into the Immediate Window and press Enter.
  • Take a look the SQL that is returned and see if there is anything odd.
  • If nothing stands out, copy the SQL onto the Clipboard.
  • Open a new Query.
  • Switch to SQL View.
  • Paste the SQL from the Immediate Window into the Query.
  • Attempt to run the Query and see what happens. Often, Access will give an error and highlight what it thinks is wrong.
  • At this point the SQL can be messed with to test out different possible solutions.
  • Also, the Query can be flipped back and forth into Design View to use the QBE Editor.
Aug 12 '15 #4
wirejp
77 New Member
Hi jforbes,

I added a breakpoint at line 18 and no errors occurred.
I followed your suggestion to type "?task' in the Immediate Window and press Enter. No result/SQL was returned in the immediate window.
I rebuilt a new form and I added the two search combo boxes. I tried the revised code below but no result returned: -
Expand|Select|Wrap|Line Numbers
  1. Function SearchCriterica()
  2. Dim myClientName As String
  3.  Dim myBillingID As Integer
  4. Dim task, strCriteria As String
  5.  
  6. If IsNull(Me.CboClientName) Then
  7.     myClientName = "[ClientName] like '*'"
  8. Else
  9.     myClientName = DLookup("ID", "queryBillingsPremium", "[ClientName] =  #" & Me.CboClientName & "#")
  10. End If
  11.  
  12. If IsNull(Me.CboPremiumInvoiceNumber) Then
  13.     myBillingID = "[BillingID] like '*"
  14. Else
  15.     myBillingID = DLookup("ID", "queryBillingsPremium", "PremiumInvoiceNumber] = #" & Me.CboPremiumInvoiceNumber & "#")
  16. End If
  17. strCriteria = myClientName & " And " & myBillingID
  18.     task = "Select * from queryBillingsPremium  where " & strCriteria
  19.         Me.frmInvoicePremiumBalanceSubform.Form.RecordSource = task
  20.     Me.frmInvoicePremiumBalanceSubform.Form.Requery
  21.  
  22. End Function
Kindly note the following: -

(i) the source code for the ClientName search combo box is
Expand|Select|Wrap|Line Numbers
  1. SELECT [qryBillingsPremium].[ClientName], [qryBillingsPremium].[BillingID] FROM qryBillingsPremium ORDER BY [BillingID]; 
(ii) the source code for the PremiumInvoiceN umber search combo box is: -
Expand|Select|Wrap|Line Numbers
  1. SELECT [qryBillingsPremium].[PremiumInvoiceNumber], [qryBillingsPremium].[BillingID] FROM qryBillingsPremium ORDER BY [BillingID]; 
Aug 13 '15 #5
jforbes
1,107 Recognized Expert Top Contributor
I wouldn't expect an error to be displayed. Doing all these things in Debug Mode are to just peek into the code and see what is going on.

I'm surprised that there wasn't a value for task, the variable should have something in it since it was set in the line previous to the breakpoint.The only thing I can think of is that the code wasn't running when entering ?task into the Immediate Window. Was the code running when you debug.printed the task variable? Or to make it easier, maybe we should put
Expand|Select|Wrap|Line Numbers
  1. debug.print task
in between line 17 and 18 of the original code.

To backup a step and possibly to help understand the debug functions, you may want to take a look at this from Microsoft: https://support.microsoft.com/en-us/kb/108438
and NeoPa wrote a cool article that has some useful information in it here: http://bytes.com/topic/access/insigh...-debugging-vba
Aug 13 '15 #6
wirejp
77 New Member
hi jforbes, referring to the code in post #3 and following your explanation in post #6, when I enter for example ?PremiumInvoice Number("PBM13-01") in the Immediate window and Press Enter, I receive a Compile error: Sub or Function not defined
Aug 14 '15 #7
jforbes
1,107 Recognized Expert Top Contributor
Do you have a function named PremiumInvoiceN umber() and is it defined as Public? If you don't have a function by that name, then you would get the error you are getting.

Did you ever find out if there was anything in the task variable, or have you fixed that and moved on?
Aug 17 '15 #8
wirejp
77 New Member
Hi jforbes, I neither had a function named PremiumInvoiceN umber() nor it is defined as a Public variable. There was not anything in the task variable. I am not very good at VBA coding. I am now trying a different approach to accomplish my goal. Instead, I am now using one combo box to search for the Premium Invoice numbers using the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboPremiumInvoiceNumber_AfterUpdate()
  2.     'Moves to PremiumInvoiceNumber field and
  3.     'finds records where "Premium Invoice Number" matches whatever is selected in the combo box
  4.     DoCmd.ShowAllRecords
  5.     Me!PremiumInvoiceNumber.SetFocus
  6.     DoCmd.FindRecord Me!CboPremiumInvoiceNumber
  7.  
  8.     'Set value of combobox equal to an empty string
  9.     Me!CboPremiumInvoiceNumber.Value = ""
  10. End Sub
  11.  
I can now select an invoice number from the combo box and it will bring show all of the records related to this invoice. My only problem now is that I want to print this list of records from the form to a report. I found a link in the forum: http://bytes.com/topic/access/answer...rm-into-report, along with Allen Browne's link http://allenbrowne.com/ser-50.html which explain the method of printing multiple records from a form to a report. I have not had a chance yet to work through the information. If I have any problems, I will ask for some more help.
Aug 17 '15 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
1995
by: Paul | last post by:
I have some option boxes and combo boxes that looks up values on a separate table (i.e. campus table with campusID & campus name fields) When I choose a selection from the combo box, it puts the information in a main table when I do ADD RECORD. Only the campusID (a number) gets added in the main table. How can I get the corresponding value displayed on the table (i.e. campus name) instead of the equivalent campusID (which is unique).
1
1708
by: Cillies | last post by:
Hi! I have a form that displays information using DLookUp. Now at present I am going into the query and entering criteria that I want the form to display. i.e. the form is for a sports team and I will enter in the team I want to display and the season I want displayed. therfore recieving the results of the team that played on the selected season. Now I want to use a form to call the results form using drop down
1
1728
by: Cillies | last post by:
Does any one know if it is possible to search a database by using combo boxes from a form. I have a form which includes comboboxes. I want to know If I can use these combo boxes to search, i.e. I have a location combobox, and If I wanted to select all entries from that location, would it be possible to enter a find mode like you do in Filemaker pro 5.5 then select the relevant location coupled with any other search data, click find and...
0
2373
by: Andrew | last post by:
Hello, I am trying to create a chart whose underlying query is linked to 2 combo boxes on the same form. I want to pass the values from the combo boxes into the chart query to allow the chart data to be manipulated easily. The combo box uses "Row Source Type" = Table/Query. The "Row Source" references a query to extract a distinct list of data.
4
2589
by: freeskier | last post by:
Currently I have some letters set up in Word which merge off of an Access database. A window pops up and asks for the parameters of the query to be entered in a text box. Is there any way that I can use a combo box instead? If I merge directly in Access using VBA will I be able to use combo boxes? I have read that using VBA in this manner may cause some security problems. Thanks again for your time
5
1880
by: Simon Jans | last post by:
Hello I'm having serious troubles creating a seach form showing data from multiple tbls, I'll try to explain my problem using a simple example by the way I'm using Acces 2003 tblEquipment equipment ID description brand ID price ...
8
3350
by: hollinshead | last post by:
Hi there i have bit of an issue. i haver this database that is purely used for searching records under certain criteria. This criteria is chosen by the user on a form using list boxes and combo boxes. So when the criteria is chosen the user selects a click command button and then the results are displayed on another form. I have successfully created the code for the combo boxes on the form.The list boxes are are the problem which are set to...
6
3563
by: mercout | last post by:
Hey, I've been trying to create a search form in access for a while now, searching through books and emails. I have the search form set up with 11 combo box's, 3 text box's, a view button, and a clear button. Once the user enters certain fields, all of the boxes do not have to be filled and clicks the view button, a report will pop up with detail of what was found.I found this code...
1
1994
by: Bhawesh9975 | last post by:
Hellow, I am trying to make Railway time table in excel through Visual basic. I have 2 combo Boxes (From and To)& Multiple Text Boxes to populate Train No, Train Name, Arrival & departure (TextBox 1-2-3-4) respectively. When I select Combo box 1 & 2, data is populated in all cited 4 text box correctly(1-2-3-4). But in text box no. 5-6-7-8/9-10-11-12 etc are also populated the same data as appear in text box no 1-2-3-4. Coding which I create is...
1
925
by: wgreen | last post by:
I have not done any access work in a lot of years and I cannot figure out my current problem. 1. I have a single table database that has 30 columns. 2. I am creating reports to extract certain data from the records. 3. None of the records have all of the fields filled in. 4. I have created queries for each type of report that I need. All of the queries work but they all return there data from all of the records in...
0
7893
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8259
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6698
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3889
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3932
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2408
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1495
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1243
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.