Hi there, I am new in VBA. I created a query from a table that lists several fields. Every time the query is used, it asks the user to enter the part number he/she wants to see and the quantity to print. I created a report from that query and it works fine. Once the user enters that information the report is printed/viewed. Instead of the user enter the information, I created a form with two combo boxes. So the user can select the part number from the combo box and the quantities from the other combo box. I can't connect the values from the combo boxes to the report. the report continues asking for the data. This is what I have so far. I removed the data from the quantity combo box for now. I am using Access 2010. - Private Sub OpenSPKanbanReport_Click()
-
On Error GoTo Err_OpenSPKanbanReport_Click
-
-
Dim stDocName As String
-
Dim strQueryName As String
-
Dim strPartNumber As String
-
-
stDocName = "BARCODE SP kanban report w_trigger"
-
strQueryPart = [PART_NO] = Forms![frmMain]![cboPartNumber]
-
strPartNumber = Me.cboPartNumber.Value
-
-
-
DoCmd.OpenReport stDocName, acPreview, "SP kanban query", strQueryPart
-
-
Or
-
-
DoCmd.OpenReport stDocName, acPreview, , strPartNumber
-
-
-
Exit_OpenSPKanbanReport_Click:
-
Exit Sub
-
-
Err_OpenSPKanbanReport_Click:
-
MsgBox Err.Description
-
Resume Exit_OpenSPKanbanReport_Click
Hi echamorro. Welcome to Bytes.com. Please use Code tags when posting code. To do that, just click the [Code/] button and paste your code between the tags.
Try setting the value of strPartnumber like this: - strPartNumber="[Part_No]='" & me.cboPartNumber & "'"
The where string parameter requires the name of the field and the comparison operator to be used. Also, since it is a string, the value must be in quotes. I used single quotes but you can use double quotes if you want. It makes the code a little trickier, in my opinion. However, if a part number can have single quotes within the part number, then you have to take additional action to prevent problems.
Be sure to remove the prompt in the query itself. That will stop the pop-up prompt from happening.
Jim
4 4218
Hi echamorro. Welcome to Bytes.com. Please use Code tags when posting code. To do that, just click the [Code/] button and paste your code between the tags.
Try setting the value of strPartnumber like this: - strPartNumber="[Part_No]='" & me.cboPartNumber & "'"
The where string parameter requires the name of the field and the comparison operator to be used. Also, since it is a string, the value must be in quotes. I used single quotes but you can use double quotes if you want. It makes the code a little trickier, in my opinion. However, if a part number can have single quotes within the part number, then you have to take additional action to prevent problems.
Be sure to remove the prompt in the query itself. That will stop the pop-up prompt from happening.
Jim
Thanks, it worked! Now I have another dilemma I need to use both fields. This is what I have done, but it is not working. Can you please help again? -
stDocName = "BARCODE SP kanban report w_trigger"
-
strQueryName = "SP kanban query"
-
strPartNumber = "[Part_No]='" & Me.cboPartNumber & "'"
-
strQuantity = "[Count]='" & Me.cboQuantity.Value & "'"
-
DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strPartNumber & "AND" & strQuantity
-
Or
-
strSQL = "[Part_No]='" & Me.cboPartNumber & "' And [Count]='" & Me.cboQuantity & "'"
-
DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strSQL
-
-
-
Thanks I found what I was doing wrong. First of all, the quantity field is double, not string. I converted the string to double and it worked.
The only thing I can't figured out is to use second option (please see code below) which uses the two field separately instead of a single SQL statement. -
this code works.
-
strSQL = "[Part_No]='" & Me.cboPartNumber & "' And [Count]<=" & CDbl(Me.cboQuantity.Value)
-
DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strSQL
-
-
-
'this code doesn't work
-
'strPartNumber = "[Part_No]='" & Me.cboPartNumber & "'"
-
'strQuantity = "[Count]=" & CDbl(Me.cboQuantity.Value)
-
'DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strPartNumber & " AND " & strQuantity
-
Could it be because you are using <= in the first and = in the second?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Greg Strong |
last post by:
Hello All,
In the past I've used a combo box with the 'row source' being an Access SQL
union query to select "All" or 1 for only 1 criteria in a query. An example
is as follows:
SELECT 0 As...
|
by: edaddyj |
last post by:
Hello, I have a db that tracks Jobs that our company does. Each job has a unique JobID. I have a report that groups all my jobs by JobStreetAddress. Since some jobs may be at the same address I...
|
by: ChadK |
last post by:
I am trying to open a report based on what the user selects on a form.
Each individual criteria works but when I try to combine to pass
multiple criteria it doesn't. I have read what I can find on...
|
by: Tlou5831 |
last post by:
I am attempting to compare 2 tables in my DB and find unmatched criteria.
There are 2 different fields in each database that need to be compared.
Tbl_AppUsers
Role Settings
INQ ...
|
by: jinalpatel |
last post by:
I have two tables
MainFirm
Name
Address
City
State
Zip
County
|
by: Neil Cho |
last post by:
Hello,
I am VERY very new to MS Access and here is my question -
I created a form, which has various drop down menus and I want the users to select different criteria and run a report. So it's...
|
by: amaxie |
last post by:
Hi there,
I want to create an output that would automatically display the results (totals only, no records) of multiple queries - more specifically, these queries all draw from the same table, but...
|
by: M0ji |
last post by:
I have created an update query to auto populate the value of one field based on the value of another field.
I have two fields "Level" and "Document"
if Document is A, Level needs to be 1
if...
|
by: shawnrye1980 |
last post by:
I have a table that has 15 managers and I have queries that I need for there employees I have 5 union all queries because there are too many where clauses, I have done this and based off the main...
|
by: cnstarz |
last post by:
Hi! I'm basically emulating a split form by having a subform in datasheet view that displays all the records of a table. Clicking on a record in the subform populates the mainform with the record's...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |