Hi, I am making a form to enter inventory into a database. We have two fields for the location. They are the building and the room. I would like both to be combo boxes, with the choices in the second box being dependant on the choice in the first. IE, if you pick building1 as your building, the row source for the room field would be all the rooms in building1. Know any tricks to do this??
View RepliesI have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct -
SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click()
If Right(strsql, 1) <> "'" Then 'check if statement was built
Else
strsql = strsql & ";" 'add trailing ; to statement
MsgBox strsql
DoCmd.OpenReport "tblincidents", acViewNormal, , strsql
End If
End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
Runtime error '91'
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code:
Option Compare Database
' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
What can be causing this?
Thank you in advance
Good afternoon all,
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
I get this error message, " object variable or with block variable not set", when I'm in the design view of a form wanting to use the command button wizard. Using a brand new database/blank I imported a table from my database in question. I then tried to make a new form and put a button on it. It worked for awhile but then when I close the database and reopen it to add another button to the same form, I get the error message again. It isn't a problem that is specific to my homeschool database. It affects all the database I use/maintain as well as any new "test" databases I've made trying to figure out this problem. I looked at references before and after the error occurs and there is not difference. I've also looked at code but don't see anything code that requires variables to be defined. In the case of the test database with one table and one form there is no code to look at until I make a button before exiting and even then, I don't see anything weird.
I would appreciate any help you can offer.
I've attached the original database I was working on when the problems began.
Is it possible for a database program to adjust security levels on certain reference libraries when installed on ones computer, thus making all other database act up that are using those libraries?
Thanks,
Kris
Hey
I have two list box.
the first box give the names of states.
The secound box uses the state name from box one. Then displays all colleges within that state.
This is an event. I am trying to get a grasp on the difference bettween DAO and ADO before coding.
At this point I like to know how to set the row source of the secound list box choice.
If you have the time to provide the compleet VBA; I will enjoy it.
Thanks a bunch
tom
Hi all,
I've got a form in my database that enables you to print or view reports, which I inherited. There is a list box that is automatically populated with all of the reports listed in the Reports tab of the database window. The list box row source says the following:
SELECT msysobjects.Name FROM msysobjects WHERE (((msysobjects.Type)= 32764));
However, I have been asked to create an identical report list that only shows some reports from the database window.
Does anybody know how I could modify the above code to only get reports beginning frm_id for example?
Many thanks
Hi, i'm having troubles using the wizard to enable me to insert fields all in one form.
I was wondering if anyone know a source code so when i start a new form from scratch, no wizard to help me. That I can use the event procedure to enable me to look up the source or the Control Source using the text field box
Thanks
I have a form bound to a query.
I have command button to open the form but I want to change the query bound to it when it opens.
I've tried changing the row source etc in an event... No luck.
Any ideas??
Hi all, at the moment all of my field are control sourced. I heard there is a way whereby the field could be unbound and one button it the control source for them all. Im not entirely sure what this means or how to go about it. But i think it would solve the problem of if my users start entering data into a record and then decide to leave it, this record is automatically added to the table, with little or no data. As the data in the table is used for statistics, the table showing records which are not actually there is causing problems. Im not sure if i explained this well, but i would be grateful of any help. Thanx
View 1 Replies View RelatedI have a subform listing items for purchase. Some are services and are not taxable, some are products and are taxable.
So far, I am calculating the sales tax for each record (item paid for), based on whether the thing is taxable or not and putting that result in a text box (txtSales_Tax) on the form. Since these calculated amounts aren't stored in a field, I can't SUM them, since SUM only works on data in table fields. Can someone tell me how to get a total?
Otherwise, I'll get rid of txtSales_Tax entirely, if someone can tell me how to write a SUM function, as a Control Source, that includes an IIf, or a WHERE, so it only adds items where the yes/no field Taxable=True.
I've got an IIf with a SUM function in it but, even in the footer, that only works for the current record. I need a SUM that has a nested IIf or WHERE in order to include all the records.
I have a drop down list of Last name and first name.
I want this to be the only unlocked feature on the page. When a name is selected the other information is shown (the persons cc#, Exp date, phone number, email etc.)
I am almost sure its a control source, but i'm not 100% as i'm relatively new to Access..
Another issue I would like to know is how to show the Last Name, First Name in the selected drop down after its selected, currently its only showing the Last name.
Thanks in advance
I have a form with several drop dpwn lists in it. I want the choice of each on to narrow the choices of the next drop down list.
I have see a few examples on this site on how this is done but at this point I dont understand how to write the information. I have taken what another person had posted and dont really get what to do next. Do I paste the info in the rowsource box and then build the query? The example I saw had expr1 and expr2 in the querys so I was lost.
Thanks, Joe
1st drop down list row source
SELECT DISTINCTROW tblProjects.ProjectID, tblProject.Poject FROM tblProjects ORDER BY tblProjects.Project;
2nd drop down list row source
SELECT DISTINCTROW tblLocations.LocationID, tblLocations.Location FROM tblLocations WHERE (((tblLocations.ProjectID)=[Forms]![frmMain]![cboProjects])) ORDER BY tblLocations.Location;
In my contacts datababse I have a main table where all the is stored for my contacts.
I have a form that I use to enter and display the information from, Because I have more the one contact at a given company I have created a seperate table that contains all of the contact details for each office.
What I would like to be able to do is when I select a office on my form Access will populate the the rest of the fields in this section with the relevant information.
How do you do this?
Hi!
I have those (simplified) tables that are linked with an n:m relation
persons:
#ID#FullName#
trainings:
#ID#startDate#
training_members:
#ID#trainingID#personID
Now I created a form to fill the training_members table with the participants. For the form I'm using a dropdown list filled with the names of the people from the persons table. What I now want is that the dropdown list only shows the values that are not already used before. So I changed the SQL statement of the row source property:
SELECT persons.ID, persons.FullName FROM persons WHERE persons.ID Not In (SELECT personID from training_members inner join trainings on training_members.trainingID = trainingsn.ID) ORDER BY persons.FullName;
I also added "me.recalc" to the after_update event of the dropdown list.
It does work - but there's one problem left: The SQL query doesn't include the just selected value. Because only values that are included in the list are accepted, the name of the person isn't shown in the dropdown.
Do you know how I can update the SQL statement so that the just selected entry is as well mentioned in the list? The abovementioned form is displayed as "Continuous Forms", so I don't know if it is possible to access the recordset the dropdowns are located into...
Thank you in advance!
is it possible to change the control source of an unbound text box ?
For example i have a textbox which simply displays the number of current records displayed i.e. =DCount("*", "queryname")
Now i wanted that with the click of a button (or something), i wanted to change it's controlsource to =DCount("*", "queryname2") ... however, as a result i got #name instead of the correct count.
Thanks
Can I build a form without it having a table or query. All unbound controls?
If so how would I preform a OnClick event to populate a combo box using code from one of my tables? Then have the form lookup the values that belong to the value in the combo box after I make a selection?
I have created a new field in a Form and named the Control Source "Active". However, when I vew in Form View there is a "0" in the field and it also does not allow me to type anything in the field.
Any help would be greatly appreciated.
Hi,
I have a form with a subform.
When i click a button on the "main" form i'm able to change the source object of the subform. But the subform now shows the records which are already present i want the subform to start with a new record.
Who can help me out ?
Thanks in advance
I have a drop down list of Last name and first name.
I want this to be the only unlocked feature on the page. When a name is selected the other information is shown (the persons cc#, Exp date, phone number, email etc.)
I am almost sure its a control source, but i'm not 100% as i'm relatively new to Access..
Another issue I would like to know is how to show the Last Name, First Name in the selected drop down after its selected, currently its only showing the Last name.
Thanks in advance