If Not IsNull(Me.[StartDate]) And Not IsNull(Me.[EndDate]) Then
strCriteria = strCriteria & "tblWO.Created Between #" & _
Format(Me.[StartDate], "m/d/yyyy") & "# And #" & _
Format(Me.[EndDate], "m/d/yyyy") & "#"
What I am trying to do is trigger a message box from the onclick of a command button on Form 2 and read the listbox # records on Form 1. It's gotta be something simple in my syntax. Search hasnt provided this scenario for me. I have tried these and more:
'If Forms("frmFilter").RecordsetClone.RecordCount = 0 Then
'If IsNull(Forms("frmFilter")).Listbox1 Then
'If Forms("frmFilter").Listbox1.RowSource = 0 Then
'If Forms("frmFilter").Listbox1.RowSource = Null Then
'If [Forms]![FrmFilter]![Listbox1.recordcount] = 0 Then
'If Forms("frmFilter").Listbox1.RecordCount = 0 Then
'If Forms("frmFilter").Listbox1.RecordCount = "" Then
'If Forms("frmFilter").Listbox1.RecordCount = Null Then
MsgBox "Sorry, no records meet your chosen dates. Change the dates and try again."
I have been helped out so many times in the past here I thought I would try my luck again.
Is it possible with the use of option buttons to change the row source of a listbox? I want to use the listbox for a search criteria and thought it might be easier to have 1 listbox been updated from the choices made using a group of buttons. I would like like the row source to be based on different tables maybe I might need to use queries.
I've been trying to determine what recordsource to apply to a listbox to get the following results for its three columns:
Column1 Column2 Column3 AddressID Address Owner
The data is being pulled from two tables:
tblAddress AddressID (pk) HouseNum Street Apt
tblOwner OwnerID (pk) AddressID (fk) LastName FirstName Current
Problem: I need each record from the Address Table to be shown with the most current Owner information from the related table. The most current Owner Information record is identified by a checkmark in the 'Current' field.
I've tried the following queries:
This query gives me duplicate addresses for each instance of owners: SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID;
This query gives me only addresses that have a current owner, the problem is, sometimes a house won't have any related owner record in the owner table: SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID WHERE tblOwner.Current = True;
Can this even be done with a query? This is driving me nuts. Help!
Using this code as part of a module to open a report in print preview and set it's recordsource to the sql I have written earlier in the code. The problem is when I run this it only prints the report it won't show it to me in print preview like I want. Anybody know what's wrong? Thanks...
This is driving me mad. I'm trying to copy a table and form from a template. They both copy fine, but when I try to change the new forms recordsource property, nothing happens.
Private Sub CreateQuiz_Click() 'copy structure from existing BLANK table a = InputBox("Name of new table", "Create new Quiz") DoCmd.CopyObject , a, acTable, "BLANK"
DoCmd.OpenForm ("BLANK") Forms!BLANK.RecordSource = a Forms!BLANK.Close DoCmd.CopyObject , a, acForm, "BLANK" Forms!a.RecordSource = a
Hi All I have a Main Form, This Main Form has a Subform, the SourceObject for the subform is another form. What I am trying to do is to set the RecordSource for the SourceObject Form from within the code behind the main form so i can use the same form with various queries. Appreciate any help - thanks
Hi everybody, I have a form with its tabbed subforms' recordsource determined by the code:
Private Sub childTvl_Enter()
Me.childTvl.Form.RecordSource = "SELECT * FROM tblTvlInput WHERE tblTvlInput.Dept = """ & Forms.frmBudget.txtDept & """"
End Sub
This is to filter the huge data by department and only allowing the specified department to view their code. However, I find it slow and pesky as it only shows the required information when the user clicks on the child. I have tried putting the code above elsewhere but to no avail.
Is there a better way of speeding the filter as well as showing the user his own records when he clicks on the tab?
I am trying to correctly code a Record Source value in a Form's 'On Open' Event . I first captured parm data from the calling Form, (Last Name, and First Name) to be used in the query as follows:
Private Sub Form_Open(Cancel As Integer) Dim intI As Integer intI = InStr(Me.OpenArgs, ";") SearchFirstName = Left(Me.OpenArgs, intI - 1) SearchLastName = Mid(Me.OpenArgs, intI + 1) Me.RecordSource = "SELECT tblName.LastName, tblName.FirstName WHERE tblName.LastName = SearchLastName and tblName.FirstName = SearchFirstName" End Sub
If I leave off the 'WHERE' clause, I get all the records in the table, so I am sure the syntax in that area is all wrong.
Is there a way to set the recordsource for a form before it opens? I've got Me.recordsource = "QryNewCaseForm" on the On Open event of the form but I toggle between recordsources once the form is open and if I close it while the recordsource is "QryCaseForm" it won't open as "qryNewCaseForm" the next time I open it.
He wanted a field to be in the subform which was originally part of the parent form and is still part of the parent table.
(Hence, when I change the field in one subform line it would change for all other subform lines related to the current parent record) He insists to put this in the subform and it should be Editable there.
I accomplished this by basically putting a 'Parent table' join 'Child table' query in the subform so I could have access to this one field. Orginially I just had a query with the Child Table but to get access to that one field (and to make it editable) I made this extra join.
I don't like this design eventhough it works..... Any suggestions on how to effectively implement this.
Also I don't understand how Access still knows what to do using the Parent Link And Child Link fields (Located in the Subform Control). How does Access know to create a new record in the Subtable and not in the Parent Table when both are present in my subform's record source now.
I have a combo box that i would like to dynamically change the recordsource (what values are available in the dropdown) based off of the criteria in another field. Basically, I want to switch which query the combo points to.
I am looking to do something like...
If field1 = nulll combo recordsource = query1 (a list of values from table1) Else combo recordsource = quer2 (a list of values from table2)
I am looking to switch which query the combo is populated with, not limit the rows based on field1.
I have a report that can be used for 3 different queries. I've been trying for the last hour or so to get three different buttons on a form to open this report but give it an appropriate record source. I figured there's got to be a way that's easier than three queries, and three reports...? I was trying to use the same format as with forms : [Reports]![rptReportName].recordsource = "qryQueryName"
or variations of that. Am I way off? It keeps saying that I've misspelled the name or it doesn't exist.
I'm trying to create navigation buttons that navigate in alphabetical order. I know that the easiest way to do this is by creating a query and using the query as the recordsource for my navigation buttons, but for the life of me I cannot figure out how to actually change to the recordsource to that query for only that navigation button.
I am trying to setup a routine that will allow the user to change the RecordSource of a subform. The RecordSource is being changed yet once I move to another record the subform is reverting back to the original subform RecordSource. I am using the below command to change the subforms RecordSource...
Me.MySubformName.Form.RecordSource = MySQL I even to make a copy of the subform and I changed the table it is linked to yet I got the same result
Me.MySubformName.SourceObject = "MyOtherSubForm"
There are no functions in the record selectors that would interfere with the subforms RecordSource.
Any suggestions as to why the RecordSource of my subform will not "stick" when I move to another record?
Depending on the record on the form Projects, the subform called Products will display the correct info
And on the OnCurrent event of the form Products, it will display the correct info for another subform called Product_Info
The problem is, the subform called Product_Info is dependant on a field on the Projects form, and on a field on the Products form so I cannot directly link the Product_Info form with either the Projects of Products form
What I'm doing is changing the recordsource of the Product_Info subform on the OnCurrent event of the Products subform but for some reason it won't work
If I display the recordsource of the Product_Info subform, it displays the right recordsource triggered on the OnCurrent event of the Products subform, but the data doesn't display
I have a form that pulls data from a big joined RecordSource query. This form needs to be editable. I need to add a field on the form that will contain a value, which is actually the sum of numbers from several records. Because that sum value includes arithmetic in the query itself, I'm assuming that is why the results of the query are not editable. I tried joining the Query that gets that sum value into the main RecordSource statement, but because the sum Query is not editable, doing that renders the whole form uneditable.
I am new to working with Access forms, and VBA in general. I have a lot of experience using VBscript, though. I was asked to get this done and now I have to fight my way through it. I'm hoping there is maybe a way to write a function that will query for the sum value based on the current ID on the Form. The main form cycles through records, and the sum value is a total of points for the record shown in the form. So as each record loads into the form, I need to get the points total that matches that record's ID and display it. The points total does not need to be editable.
Any suggestions for a newbie on how to solve this?
I have a form with two subforms. One of the subforms has five subforms (subsubforms).
The ControlSource and the (query sequence for each) RecordSource of each of the subsubforms are created on-the-fly. The subsubforms have no Master/Child relationships specified. The subsubform merely display various filtered and various grouped subtotals of the 1st subform. Summing and grouping on the subsubforms prevents my using Master/Child properties.
On the AfterUpdate event on any of the filter fields on the form the ControlSources and RecordSources are rebuilt and the form becomes invisible for some reason and appears to hang, even after stepping through all the (class module) code with the debugger. Repeadedly clicking both Maximize and Restore buttons on the form's button on the Status Bar followed by clicking a 2nd form's toolbar icon causes the form to become visible under the 2nd form. The tool bar are also not visible, just a large brow bar at the top of the form. Moving the mouse over the toolbars causes individual icons to become visible or raised.
What am I missing?
Maybe I should calculate the values using DAO recordsets and backfill unbound subsubforms.
I want to write Visual Basic language in the form with one button:
I have one form named "setForm" with one button and one text box: Let's set the new table "frm2", the new form "frm2"
Dim sql As String sql = "SELECT [model].* INTO frm2 FROM [model]" CurrentDb.Execute sql DoCmd.CopyObject CurrentDb.Name, "frm2", acForm, "frm1" Forms!frm2.RecordSource = "frm2"
The last statement code doesn't work: It is said that Microsoft Access cannot find the form "frm2" referred to in a macro expression or visual basic code.
I have a dropdown menu which has a lisrt of dates (mmm yyyy), and when one is selected it updates 2 boxes with 01/mm/yyyy and 30 or 31/mmm/yyyy
Private Sub cmbDate_AfterUpdate() Me.frmData.Form.RecordSource = "SELECT * FROM qry_NatxReg WHERE [Company Name]='" & cmbCompanyName & "' AND [Time of Attempt 1] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#" Me.frmData.Form.Requery End Sub
When i choose June, it works fine and gets all the june dates from [Time Attempt 1]
But when i Choose July, it includes June.
I only have data for June and July as it's still in testing. I can't wrap my head round it!!
But I don't know what to put for an expression. I would like to use the same form but base the RecordSource on a query called "Receiving Search by Date". :eek:
I have report in which I have to do some custom calculation in the detail section (using VBA) of the report. How do I access the fields provided by the report, which should come from the query or table fields of the query or table set in the recordsource property of the report in design view. I am able to access some of the fields but not others. Could someone explain to me how this is done.
I have a recordclone combo box for navigating between 240 company records, so the form will go to the correct record on updating the combo. Instead of clicking on the down button and scrolling through the list, I would like to enter letters into the box and update the record source automatically as I enter them.
I'm not sure what events to use since there appears to be very subtle differences between them (eg between 'on dirty', 'on change' and 'before update').So when I type in 'T', only those companies that start with 'T' are viewed in the combo box.I have discovered the VBA 'dropdown' command which would be useful for the user.
Code:
company.dropdown Table_Company Company_ID: autonumber Company_name: text
The name of the form is "form1" and the combo box name is "company".The record source for the combo box is "query_company". In the form the ID field is hidden and the company name is not hidden.The criteria I have for the combo would be something like this.
Code:
SELECT Table_Company.Company_ID, Table_Company.Company_name FROM Table_Company WHERE (((Table_Company.Company_name) Like [forms!]![form1]![company] & "*"));
I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:
I'm using Access 2000 and want to filter the datas in a form based on a query. The query written as an SQL string has already a length of about 2000 characters. Now I want to filter this datas with information I get from a combo-box, and changed the RecordSource with additional statements in the WHERE part. Therefore the new RecordSource information can exceed the 2048 characters.
So I thought to use the filter-argument of the form instead. But the problem I have now is, that I would have to use "((First(T_Softwareversion.Softwareversionsname))>='B14')" as part of the filter argument. With using this I get the runtime error 3096. Using this as part of the WHERE part in the RecordSource works, but it works not using it in the filter argument.
Does anyone know a workaround for the first or the second problem?
