so I have been working on this form for a while and I'm really a newb at this
maybe it easy maybe its not but i just couldn't figure this out
I'm so frustratated that I'm willing to pay(beer money for help)
well anyways I have this form
http://i27.photobucket.com/albums/c176/rlan214/WorkAssignForm1.jpg
at the side you can see that there are 3 filters
1st has check box (check box is always checked) for the date
2nd check box for Target Location
3rd check box for ForkliftID1
I also don't know how to change the date format in my date
its in yyyymmdd format right now and I want it mmddyyyy
In the middle theres a blank, and thats where I wanted my
query to show
here's my query
http://i27.photobucket.com/albums/c176/rlan214/WorkAssignQry1.jpg
I have created a form for a table which contains ~600 movies and their name, genre, rating, director, year it was made, and length (min).I need to be able to enter numbers into the two Year boxes, and then it filters the movies in my database and only shows me records from between those two years. The years in my database are just in one column in the format of: XXXX e.g. 1996 etc..I've tried the code:
Code:
Private Sub Year2_AfterUpdate() Me.Filter = "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2 Me.Filteron = True Debug.Print "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2 End Sub
Year1 and Year2 are the boxes Year: and To: respectively. [Year] just being the column name which contains all my years.When I try to run my query it shows me my records, but it shows me all of them! It doesn't filter it at all!
In the Purchase Order details form a Supplier has to be chosen via a combo box. Based on that another combo box in the subform displays products only from this supplier (Products table is linked to supplier table).
I managed to let the subform combo show only relevant products using criteria referring to the main form combo box. Also other product data will show accordingly in text boxes. So far so good.
However when I close the form and reopen it the subform combo box is blank, other text boxes still show the right values. If I remove the filter criteria for the subform combo then all fields show all data correctly. (However the combo box is unfiltered again 8-/)
So somehow the 'criteria' prevents the combo to show the value that was previously chosen.
A form I created contains buttons for the user to restrict the display- showing records that are tasked to one specific employee. So their choices are Employee a, employee b or all employees. This is a process I use a lot :) This last form opens with NO records displayed. When a button is clicked the records show correctly. Any idea what to check? Thanks,
Actually, I don't think i've done this right, but here's the goal I wish to achieve.
I have 2 tables, one containing contract info and another containing client info. I have the 2 related using the client name field. I have a form which is based on the contract table which contains a drop down field of client names. When I select a client, I want it to update a read only client address field within the form, just for display purposes.
Is this easy to do and can anyone point me towards some examples on how to achieve this???
Can't seem to locate a similar situation in the forum for this problem. I have a form based on "qryProjects". I have another form "Menu" which has cboType, lstStatus and lstSubtype. I thought that I could select criteria from each and, with a command button, have the form open up filtered with the three criteria. In qryProjects pane I have as the criteria for each of the three fields:
[Forms]![Menu].[lstStatus] - Value list Bound column = 1
[Forms]![Menu].[cboType] SQL in cboType = SELECT DISTINCT [SubType].[Type] FROM SubType; Bound column = 1
[Forms]![Menu].[lstSubtype] = this is filtered on the After Update of cboType SELECT [SubTypeID], [Subtype] FROM Subtype; Bound column = 1
I have them on separate lines in the query. When I run the query, it only filters on cboType and not the others. If I place the criteria all on the same criteria line, I get nothing.
Is my criteria in the wrong place? I was originally trying to make the form completely "unbound" and build a SQL string to pull the records but don't know if that is possible. My purpose is to eliminate macros that I was using as it took over 2 minutes to open the form with the BE residing on a shared network drive.
If anybody could shine some light it would be much appreciated. I have 3 tables: Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time) Tasking table---*TaskingID, TaskID, EmployeeID employee table---*EmployeeID, Name(txt) The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy. If anybody can help i would be very grateful
If anybody could shine some light it would be much appreciated. I have 3 tables: Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time) Tasking table---*TaskingID, TaskID, EmployeeID employee table---*EmployeeID, Name(txt) The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy. If anybody can help i would be very grateful
I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.
Problem 1 I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?
Private Sub cmdReport_Click() Dim varItem As Variant Dim strDocName As String Dim str1MainCate As String Dim str2MainCate As String Dim str2MainCateCondition As String Dim strDate As String Dim strSQL As String Dim strFilter As String ' Build criteria string for 1st ComboBox For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2nd Combo Box For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1toggle button condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _ "WHERE NewsClips.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";" ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.dateTo, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If ' filter string strFilter = strDate ' Open report strDocName = "RptCateDateQry" DoCmd.OpenReport strDocName, acViewDesign, , strFilter With Reports(strDocName) .RecordSource = strSQL .Filter = strFilter .FilterOn = True End With DoCmd.Save acReport, strDocName DoCmd.OpenReport strDocName, acViewPreview Exit_cmdReport_Click: End Sub
Problem 2 I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.
Private Sub cmdOK_Click() On Error GoTo cmdOK_Click_Err Dim blnQueryExists As Boolean Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View Dim varItem As Variant Dim strDate As String Dim str1MainCate As String Dim str2MainCate As String Dim str1MainCateCondition As String Dim str2MainCateCondition As String Dim strSQL As String ' Check for the existence of the stored query blnQueryExists = False Set cat.ActiveConnection = CurrentProject.Connection For Each qry In cat.Views If qry.Name = "QryCateDateForm" Then blnQueryExists = True Exit For End If Next qry ' Create the query if it does not already exist If blnQueryExists = False Then cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips" cat.Views.Append "QryCateDateForm", cmd End If Application.RefreshDatabaseWindow ' Turn off screen updating DoCmd.Echo False ' Close the query if it is already open If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then DoCmd.Close acQuery, "QryCateDateForm" End If ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.textStartDate, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If
' Build criteria string for 1MainCate For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2MainCate For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1MainCate condition If Me.optAnd1MainCate.Value = True Then str1MainCateCondition = " AND " Else str1MainCateCondition = " OR " End If ' Get 2MainCate condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _ "WHERE NewsClips.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _ str1MainCateCondition & strDate & ";" ' Apply the SQL statement to the stored query cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("QryCateDateForm").Command cmd.CommandText = strSQL Set cat.Views("QryCateDateForm").Command = cmd Set cat = Nothing ' Open the Query DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point ' DoCmd.Close acForm, Me.Name ' Restore screen updating cmdOK_Click_Exit: DoCmd.Echo True Exit Sub cmdOK_Click_Err: MsgBox "An unexpected error has occurred." _ & vbCrLf & "Procedure: cmdOK_Click" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description:" & Err.Description _ , vbCritical, "Error" Resume cmdOK_Click_Exit End Sub
Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.
I would like to have a user enter a start date and an end date into two textboxes on a form. The two dates will be used to query a table. I would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.* FROM Hoods INNER JOIN Transactions ON [Hoods].[ID]=[Transactions].[BoxID] WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate]) ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and txtEndDate to @EndDate in the VBA code of the form? How would I open or print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the report from the above query, or should I do it another way? Can anyone direct me to some code that does all of the above or something similiar?
I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.
Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."
So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:
The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.
When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.
As for my cascading combos, here are the settingsMatterTypeIDRow Source:
SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter; On Change Event:Me.cboAtty.Requery
[Code] ....
I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter
data 3 times:Enter Parameter Value: Atty Enter Parameter Value: Plgl Enter Parameter Value: Who?
Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.
I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.
problem when I am trying to use Form to filter data from a cross tab query though I have already defined the parameters. The SQL is as under :
//Code start PARAMETERS [FORM]![START_MONTH] DateTime, [FORM]![END_MONTH] DateTime; TRANSFORM Round(Sum(CLng([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block = PRODUCTION.FIELD_BLOCK WHERE (((maindata.field_block)<>"Panna" And (maindata.field_block)<>"Mukta" And (maindata.field_block)<>"CB-ONN-2000/2(NSA)" And (maindata.field_block)<>"CB-ONN-2000/2(BHEEMA)") AND ((PRODUCTION.MONTH) Between Format([FORM]![START_MONTH],"dd-mm-yyyy") And Format([FORM]![END_MONTH],"dd-mm-yyyy"))) GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block PIVOT PRODUCTION.ACTIVITY; //Code End
When I save it prompts for the Start and End Month and When I run the query it pops up the form twice...
I have one form which houses all my product information via the use of a query and subforms where appropriate. I would like to filter it in order to find records missing certain information.
I want to filter the information using a combo box.
The combo box has a user friendly name (Product Dimensions) and the name of a query related to that name (qryfilter_missing_prod_dims - this query is the record source query for the form plus the query that has the missing info, the query is set to return all records that match ie. all records that are missing info).
I want to write a vba code to filter my form based on that query.
And i can't get it to work using the cmd.applyfilter (docmd.applyfilter me.cbo_missing)
I've also tried to use me.recordsource. when i do it filters perfectly, but i can no longer edit any of my data in the form.
How to filter my form using a query that is not in the native form's record source?
how i would filter a form using an option button. E.g i want to be able to click an option button that is called Filter user and it will filter the query based on who is logged in? and a Filter all option that will clear the filter and show all records?
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
i need to size a form so that it does not fill all the window, i have tried enabeling the min and max buttons but they will not display for some reason, i changed the borderstyle to sizable and the controlbox to yes the min and max buttons are enabled , but still not showing the min and max buttons, the close button shoes and thats it? i am running 2010.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
I have got a db when I enter a new client in the client form it saves it in the table but when I return to the form the client I have just entered is still there so I cant enter a new client it does'nt clear the text in the text box can anyone help
I have a main form that displays records with a couple of sub forms and it also has a button to open another form which is based on the same table main table but different sub forms.
All I am trying to do is open the second main form by clicking the command button and to automatically display the same main record as shown on the first form before clicking the button.
Thanks for all your help on my previous problem but I now have another one that just seemed to start out of the blue. When I open my form the record selectors don't allow me to browse through all the previously entered records. This is a huge problem because if changes need to be made I can't call up the record to do so. I plan on adding a search funtion to the database soon but for now I need to be able to search through the records on my main form. Is there something I should look for or do you have any suggestions?
I am building a relatively small database, but it is made up of a number of different tables which are fed by other tables.
My three main tables are:
EmployeeDetails Occupational Risk Profile Health Surveillance Requirements
The employee details table has fields fed by small tables such as Department, Location, Business Unit, Occupational Group etc. These are all made up of an autonumber primary key column and a variable column as described above.
I have been going round in circles with a lot of this as I am still learning.
My problem is that on my forms these fields are showing as numbers and not the text values. For example, I have one form made up for each of the main tables above but I can't get them to display the relevant text.
I did a search on here and somebody said to create query to link the tables and bind this query to a form.
How do I set up the query just to return the text part and not the number? How do I bind this query to the form if I already have the record source on a my form as a table? (can you have more than one source in the record source?) Do I create one large query with all of these fields and use this as the basis for the form?
I have created a form off a query. I made the form for adding new records only. When i bring up the form it is blank, the labels and fields don't even show. Anyone know why this is?
