Modules & VBA :: Using Form Control Value To Determine Query Criteria
Jan 26, 2014
I'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.
I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).
I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.
Things I have tried to no avail: Putting a Iif statement in the query criteria: gives an error that criteria is too complex Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run
View Replies
ADVERTISEMENT
Aug 16, 2014
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])
This works for the records with fields that have data.
Code:
Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
Code:
"" Or Is Null
View 3 Replies
View Related
Sep 23, 2013
Had a combobox so the user could choose between two data entry formats.They don't want the combobox, they want the user to enter data into one or the other textbox - and that choice to trigger the format.Two groups of text box - group 1 has a single text box - group 2 has three text box. When the user selects a text box and types the first character into it. This triggers locking out the other group choice and enables the <Validate and Edit> command button.
If the user backs out (deletes) the text in a text box.It basically makes both groups available again and it disables the <Validate and Edit> command button.Basically, if the text box Change event counts a character, it changes the text box Tag to "Bust". Then it calls a common routine that checks both text boxes.The choice won't take place if the textbox gets focus. It changes if a text box has 1 or more character typed in.
Code:
Sub WhosOnFirst()
' two groups of Required field - if one textbox in a group has a character entered first
' that group becomes the Format - enables the <Validate and Accept> button that will take the next setp
[code]...
View 2 Replies
View Related
Aug 15, 2007
I have a list box on a form and right now the list box lets me scroll through the names of people in my database. What I want to be able to do is click on one of the names in the list box, and then a set of controls adjacent to the list will be populated with data for that person so that I can edit and view their data. Is there a tutorial or person that can tell me how to do this? Thanks!
View 1 Replies
View Related
Oct 22, 2013
I have small data base with many tables, one of them a table for equipment wit related details, as below fields.
1-EqipmentID
2-SerialNO
3-Model
4-Coustmer
6-City
I created one normal method query of ACCESS "QueryEQ" on that table , but every time I want to change any criteria I have to edit manual direct to query design .
What I need to do now is to create a form with one combo box showing all "model" and after select any mode I have to press command bottom to change the criteria of the model of the existing query "QueryEQ" and run it and show the result in a subform in same form.
View 2 Replies
View Related
May 29, 2014
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
View 13 Replies
View Related
Nov 1, 2006
Hello.
I'm trying to build a query criteria for a tab control with three different tabs. They are all using the same subform in each page. I want to be able to set the criteria for one specific field to the current tab control page that is selected. Does anyone know how to do this? I wasn't able to find it on the forums...
Thanks a bunch!
View 2 Replies
View Related
May 28, 2014
I have a table that consist of pricing for different types of products:
Table name: Costs
ID TYPE EastNew EastRenew WestNew WestRenew
1 500 4100 1000 4400 900
2 501 4100 1000 4400 900
3 600 3400 900 4600 1200
4 700 3700 1300 4900 3300
I have a form that consist of many fields that the user needs to manually input data. 3 fields on that form are the criteria i need to determine which pricing to use back on the table. The first field, which name is LTYPE, is a combobox that the user needs to choose as its type (i.e 500,501,600,700). The second field, which name is EastCoast, is a checkbox (Yes/No) that tells me if it's East Coast (East = yes and West = No). The third field, which name is NewRenewal, is a combobox that the user chooses NEW or Renewal).
Example 1, if the user chooses type 501, checkbox is YES for East, Product is NEW. Then the pricing is $4,100
Example 2, if the user chooses type 700, checkbox is unchecked for NO (which is West), Product is Renewal. Then pricing is $3,300
I would like the unbound field to auto determine the pricing based on the 3 criterias chosen. I read around the forums that Dlookup would be the function for this, however, i tried many times but it did not work.
View 6 Replies
View Related
Oct 10, 2013
I have a form (named frmAddSession) with a combo box (named Band_Name) and lets say i have it displaying the band "Band A"
I have a query where the criteria is [Forms]![frmAddSession]![Band_Name]. when i run the query the results are blank. but if i type ="Band A" it works just fine.
why won't my reference to the combo box on the form work?
In a series of deductions to try and figure this out, my form only has the one combo box, and my query is based on only one table and only has the one field.
View 5 Replies
View Related
Dec 11, 2013
I have a totals query that provides an avg for each month. i'd like to be able to use a text box control (named "Date") on a form (named "Report Runner") to show only a certain month and it's avg.
I tried using this as criteria on the "MonthGroupPMC" field:
Code:
Format([Forms]![Report Runner]![Date], "yyyy-mm")
but the results came up blank.
how can i filter the results of this query to show only one month, specified by the [Forms]![Report Runner]![Date] control?
View 5 Replies
View Related
Dec 15, 2006
Private Sub btncard_Click()
Dim stLinkCriteria As String
DoCmd.RunCommand acCmdSaveRecord
stLinkCriteria = "[jobref]=" & "'" & Me![jobref] & "'"
DoCmd.Close
DoCmd.OpenForm "k_job_card", , , stLinkCriteria
End Sub
this button code opens the k_job_card form.
the form opens and says 'Record 1 of 1, (Filtered)
i have a control on the form that opens.. and i am simply trying to assign a default value to that control..
it is a textbox.. for my jobtype field (text datatype)..
is there any particular reason why i cannot assign a default value to a control if it has been placed on a form that has been opened using a form filter?
this is how the Form Data tab is set at the moment..
View 3 Replies
View Related
Feb 2, 2014
I need to pass a criteria to a query from my option group control to my query.
It contains three options 1,2 and 3.
If option 3 then Pricing Type 1 and 2
How do I make the code below working?
IIf([Forms]![FrmUserSelection]![PricingType] Like 3,([dbo_AGPricingDiscounts].[PricingType])="2" Or ([dbo_AGPricingDiscounts].[PricingType])="1",",[Forms]![FrmUserSelection]![PricingType]")
View 3 Replies
View Related
Nov 12, 2013
I have a form with a (large) number of controls which acts as a kind of read-only dashboard of data. The data is split across a number of tables (4, for now, but may grow) I've created a query which amalgamates all of the data from the tables into a single dataset and set that as the RecordSource (Snapshot) of the form. I now want to bind each of the controls to a particular field returned by that query.
However - I have a combobox (same form) which allows users to select a date and I need the controls to update with the relevant data for that date whenever the combobox is changed. The query already returns values for each field over a range of dates so the data is available within the form's RecordSource - I just don't know how to include the date criteria, as specified by the combobox, when binding each individual control?
View 2 Replies
View Related
Aug 6, 2013
I need to set calendar control as criteria for date in VBA query. I cannot find Calendar control in ActiveX tools in Acc 2010. Where is it?
Date picker seams like very slick and simple solution but I can see it only in database. As it might be solution to my problem, can date picker be used on a form as criteria for VBA query?
View 6 Replies
View Related
Oct 1, 2013
I need a module but haven't got the foggiest on where to start. I have a query that works out whether a staff member has done a sleep in based on their clocking out time (TIME OUT). I also have a column for DAY IN which it stores the day they logged in and HOUSE which indicates which house they clocked in at. What I want to do is determine whether each 'sleep in' is a weekday or weekend shift and at which house. We have different rates of pay based on the houses and whether it is weekend or not.
View 5 Replies
View Related
Apr 27, 2015
Is there anyway to determine the drive letter of a flashdrive inserted into a PC ? I have a backup routine that backs up a MS Access database to a flashdrive. I need to determine the flashdrive letter.
View 9 Replies
View Related
Jun 16, 2014
I have a table that has a field for month represented by a number. I would like to report by month but group by year. The report would group months 1-12 into year 1, months 13-24 into year 2, months 25-36 into year3, etc..... up to possible 20 years. Is there an easy way to identify and group the year? I was thinking of adding another column to the table and when I write the values to it include a year. I would rather do it some other way as the data is spilt and would require a lot of table modifications to distributed db. It would be easier to do it with a query or some code in the front end.
View 2 Replies
View Related
Dec 15, 2014
How to search for a file using the dir function, however, i need to search for a file from a main folder which may have several sub folders.
I am having no luck so far - i understand there is a recursive search facility but im not sure how i can get this to work with what i am trying to achieve.
This is the code i have for the dir function:
If Len(Dir("as-tamworth-50midlands.qa$TamworthLaminate C of CsCircuit Foil" & CofC & ".")) = 0 Then
MsgBox "This file does not exist"
Else
MsgBox "Yippee"
End If
View 5 Replies
View Related
Jun 12, 2014
I am having trouble with a If statement. I am trying to save a record into a table when an item is Selected (highlighted blue) in a list box. Keep in mind that my listbox is set to multiple selections. I have the following code but sometimes it works and sometimes it doest.
The reason why I am trying to get this to work is because when a item is selected I will add it to the table and when an item is deselected I will delete it from a table. See my code and pictures I have attached :
Private Sub list_audits_Click()
Dim strsql As String
Dim list As String
Dim id As String
id = Me.User_ID.Value
[Code] ....
View 2 Replies
View Related
May 2, 2014
How to determine if a field within a table is a primary key or part of a compound key using ADO?
View 1 Replies
View Related
Sep 17, 2013
I have a form designed to update content on a website. I'm trying to work out how to populate fields with entries from past dates.
I have set up two combo boxes for each 'spot' on the site, one for date: Feature_1_Date and one for which content spot the content was occupying Feature_1_Spot.
The idea being that these boxes would read the row from my table to find the date, and the column to find which spot the content did occupy to copy the same content into a field Feature_1_Content
From what I can gather I need to use DLOOKUP (I'm very familiar with VLOOKUP on excel) but I'm doing something very wrong as can't get it to show any content.
View 1 Replies
View Related
Feb 10, 2015
This for an app being used in Access 2003. I'm trying to (sort of) automate naming a PDF report. After the user selects a printer from the print dialog, if the printer name is like "*PDF*" then I want to put the PDF file name in the clipboard so it can be quickly pasted in the file name of the PDF printer's save dialog.
Something like this:
Code:
Dim strSelectedPrinter as string
DoCmd.OpenReport "rpt1", acViewPreview
DoCmd.RunCommand acCmdPrint
[Code]....
but that returns the name of the default printer, not the selected printer.
View 6 Replies
View Related
Aug 8, 2013
I am writing code to Refresh table links. I only want to refresh the Access table links and ignore the Excel Table links. Is there a way to determine in VBA if the table link is pointing to an Excel file?
View 8 Replies
View Related
Oct 2, 2014
I have a database with two tables. One with a schedule that lists all of the events with the person assigned to, with there start date and end date in separate columns.
My second table lists a persons leave. So I have there name and the start and end date of their leave in separate columns.
Is there a form/query I can create using VBA that will allow me to filter out all the criteria that has the same name and then a date that conflicts between the event date and the leave date.
for example,
If Name(in table 1 ) = Name (in table 2)
(if false stop)
AND,
If StartDate (from table 1) and EndDate (from table1) conflicts with StartDate (from table 2)and EndDate (from Table 2)
(if false stop)
If true I want either the query to register it OR have a form appear to show the conflicting entries.
View 1 Replies
View Related
Dec 12, 2014
I am working with the Access Treeview feature and I'd like to align the different elements in my nodes exactly below each other.
Therefore I need exact distances (if string is longer then XY, cut short, if shorter, extend to XY).
Of course I cannot work with len() and fill with spaces because letters have different widths.
Is there a way to determine the exact width of a string? e.g. in pixels?
View 7 Replies
View Related
Jul 15, 2014
Using Access 2007, can I return a value for CurrentProject.FileFormat WITHOUT opening the database?
I'm looking for a way to determine the file version of a given Access database without actually "opening" the database in Access (I don't want it to ever be visible at all or in any way).
So to open a database in Access 2007 "without opening it," I'm using:
Code:
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:Path-ToFile.accdb")
From here, there are at least two different "version" indicators that can be used, as in this example:
Code:
Debug.Print db.Properties("Version").Value
Debug.Print db.Properties("AccessVersion").Value
Now, if you run this code on an Access 2002/2003 .MDB file, you will get:
Code:
4.0
09.50
If you run this code on an Access 2007 .ACCDB file, you get:
Code:
12.0
09.50
Note two important factors:The db.Properties("Version") seems to be returning the JET version from what I can see in research. This is NOT what I'm after.
The db.Properties("AccessVersion") returns THE SAME VALUE for an '07 ACCDB as it does for an '02-'03 MDB, since Microsoft never updated it, apparently.
Now.... the one way that I have found that ACCURATELY describes the version of a given access database file is to use:
Code:
Debug.Print CurrentProject.FileFormat
...as this will always return, for example, 2.0 for an Access 2.0 .MDB file; 10 for an Access 2002/03 .MDB file; and 12 for and Access 2007 .ACCDB file (and there are others; these are AcFileFormat bitmask constants as described in Access support).
BUT, this CurrentProject object doesn't seem to be available (so far as I can see) when using the "open without opening" method described above.
SO, is there any way to return the FileFormat value without opening the database visibly? (Merely basing it in the extension isn't right either.... that would be easy but not accurate as several formats use MDB.
View 5 Replies
View Related
