hi all, Newbie to access and am making an asset tracking database for work. I have written a sub that opens a hidden form and queries it. I want to get a value from that hidden form and stick it into a combo box on my origianl form. I have got most things to work. The problem i am having is using a variale in a control form i.e. [forms]![ & strVar & ]![Field] here is the code i have so far
Private Sub Form_Search(strFname As String, strField As String, strFField As String, strWhere_Next As String)
Dim strWhere As String
strWhere = strField & " = " & "" & strFField & ""
strWhereA = "[Forms]![" & strWhere_Next & "]![ALLOCATEDTO]"
DoCmd.OpenForm strFname, acNormal, , strWhere, , acHidden
Me.ALLOCATEDTO.Value = strWhereA
DoCmd.Close acForm, strFname
the strWhereA shows the correct field but is shown as [Forms]![Formname]![ALLOCATEDTO] in my combobox, not the actual value of that form. Please help. I know this is probably a simple syntax error but am now at my wits end!!!
The following code is only for testing the usage of control variable. I want to use set statement to assign the textbox object reference to the varialbe myControl.
When I clicked the button, the system always tells me 'the object reference missing'. It seems the set statement doesn't work, but I don;t know the reason. could anyone of you please spot it out for me?
Many thanks
Option Compare Database Public myControl As control Public frm As Form Set frm = Forms!Form1
Sub Command4_Click()
Set myControl = frm!Text0 myControl.SetFocus
End Sub
Sub Command5_Click() Set myControl = frm!Text2 myControl.SetFocus End Sub
I have a table Names containing Recnum and Last Name. I have a table Grades including Recnum, Names and Grades. I have a Report which Through a query Reports the grades and averages for each name. I would like to be able to query for an individual name using an entry form. How do I get the name from the form into the Criteria in the query grid.
I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())" AgentFilter = "CASEOWNER ='" & Me.txtName & "'" DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter Exit Sub End If
I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.
Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.
Here is a brief idea of what my Table would look like, where where the data to import would be obtained from
I am trying to set a filter on a form with VBA using variables and having no success at all. The code I tried last, which seemed to be "close, but no cigar", was:
sLastSource and sBiller are global variables. When I debug this code the sFilterValue is exactly what I would plug in manually and the Me.Filter shows up as "[Field Name] = Filter Value". But it crashes on Me.Filter = sFilterValue. I have tried all sorts of combinations but nothing I've tried works. What is the proper syntax for using variables as filters?
I've inherited an Access 2010 database that needs tweaking. I have a form, let's call it CALLED_FORM, that is loaded from various places - ie there are quite a few places that do the following
This works great when called from most places but if it is called from 1 specific form there is a likelihood that some code is executed that shouldn't be.
So I want to be able to do the following
In CALLED_FORM I want to be able to skip some code if the previous form is say FORMX. For all other forms I want it executed. E.g.
If PREVFORM <> "FORMX" then code more code even more code End If
Is there a system variable for PREVFORM.
I know I can change coupleofarguments to threearguments and pass something over in that to show which form it has been called from but that is a bit difficult.
I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record. I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:
Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.
'------Code I'm Currently Working On------- Dim intX As Integer Dim EventVal As Integer Dim strX As String
intX = 1 EventVal = Val(Mid(Event_List, 7, 1)) Do While EventVal <> intX And intX < 6 intX = intX + 1 Loop
strX = str(intX) Dim E_Name As String Event_Name = [Event strX ] PMT = [PMT strX] Receipt = [Receipt strX] Payment_Type = [Payment Type strX] PIF = [Paid In Full strX]
'-----Old Code That I'm trying to simplify------- If Event_List = "Event 1" Then Event_Name = [Event 1] PMT = [PMT 1] Receipt = [Receipt 1] Payment_Type = [Payment Type 1] PIF = [Paid In Full 1] Camp_Site = [Camp Site 1] Cabin_Number = [Cabin Number 1] ElseIf Event_List = "Event 2" Then Event_Name = [Event 2] PMT = [PMT 2] Receipt = [Receipt 2] Payment_Type = [Payment Type 2] PIF = [Paid In Full 2] Camp_Site = [Camp Site 2] Cabin_Number = [Cabin Number 2] ElseIf Event_List = "Event 3" Then Event_Name = [Event 3] PMT = [PMT 3] Receipt = [Receipt 3] Payment_Type = [Payment Type 3] PIF = [Paid In Full 3] Camp_Site = [Camp Site 3] Cabin_Number = [Cabin Number 3]
I have been at this for almost 3 weeks now and I'm having great difficulties trying to get this right and working. First let me explain what I am doing and what I am trying to do.
Firstly, I am making a Maintenance database using MS access software, what I have are tables, forms and no queries or report existing so far. The forms and tables all work correctly.
After the form (Job request) has been completed I need to save/export into PDF so I am able to hyperlink it against its existing asset card elsewhere.
now what I have done is Created button, on event "on click" I have tried to go to macro builder
ExportWithFormatting Objet Type: Form Object Name: FrmMachineFault/GenMaint Output format: PDF Output file: Auto Start: No Template File: Encoding: Output Quality: Print
when I click this button it opens to save to and it works perfectly but I have to manually type the file name when it gets to the Save to section.
What I want it to do is define the name by the existing fields in the open form. Example Closed date, Effected area, Asset and title.
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation Variables: @SelectedDate (date format) (taken from a form field) @SelectedVessel (nvarchar(max) format) (taken from a form field) @SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
I have an Access form that gathers students test scores on Reading & Writing exams. Depending on those scores, there are five possible classes for them to be assigned.
I have the intervals worked out, and have created a table that contains the min reading, max reading, min writing, max writing, and class they belong in.
The scores are entered into a form as scoreReading & scoreWriting (integer)
I'm trying to figure out the best way to pull and compare the values and return the proper englishPlacement text. Its set up this way because there are multiple tables to pull from, depending on the date of the exam. Each table has different ranges for each Eng1,...,Eng5.
I'm thinking perhaps to iterate through each column and evaluate T/F, then return the englishPlacement value of the correct row. I'd like to keep this stuff in the VBA so that the code to select a table based upon date doesn't break when the intervals inevitably change requiring an additional table.
I have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
When I right click a row on the data sheet side of a split form an select "New Record" I want the curser to go to the first field on the single record side. I've placed this in the OnCurrent but it did no good.
Code:
If Me.NewRecord Then Me!Descrfiption.SetFocus. End If
Any way to set the focus to the single form Side of a split form?
I would like to copy one control, then close this form, open concrete form and pass value to control.
My code is
Private Sub Menu_Click() DoCmd.OpenForm "frmZleceniaMarzenaNawigacjaPD" Forms!frmZleceniaMarzenaNawigacjaPD.Form!Imie = Me.Imie Forms!frmZleceniaMarzenaNawigacjaPD.Form!Imie.SetFocus DoCmd.Close acForm, Me.name End Sub
The problem is, still opens the previous form, not form "frmZlecenia"
e.g If I open form x then from this form I open my modal form "frmZlecenia" and then I will click "menu" button - now form "x" is open :/, but should be "frmZleceniaMarzenaNawigacjaPD"
There is some way to open concrete form from modal form?
I have a data entry form which have a date field.This field retains the existing date after the record is saved with vba after update event.
Private Sub Form_AfterUpdate() Me.ProductionDate.DefaultValue = Chr(35) & Me.ProductionDate.Value & Chr(35) End Sub
Almost after a year of perfect working i am facing a problem.When i save the record , in date field my date and month values are reversed if date is between 1-12.although my database stores perfectly valid date. for example if date is "10-09-2014" (dd-mm-yyyy) after saving form date field becomes "09-10-2014". but if date is "13-09-2014" The date form field retains this value .
control called "FilterListBox" list box called "lstCustomer" option Box called "optCustomerType"
When the user selects an option in the option box, "FilterListBox" is updated to either "1", "2" or "1 or 2"..One of the fields in the query for "lstCustomer" is "CustomerType" and its criteria is set as follows:
if "FilterListBox" = 1 the query for "lstCustomer" returns the correct records if "FilterListBox" = 2 the query for "lstCustomer" returns the correct records
But if "FilterListBox" = 1 or 2, no records are returned.
Hi, here is a query which returns rolling sum for past5days.The resulting table has threee columns,namely,
MyDateFiled ,MyValue,Past5DaySum
SELECT tblData.MyDateField, tblData.MyValue, (SELECT SUM(a.MyValue) FROM tblData As a WHERE DateDiff("d",a.MyDateField,tblData.MyDateField) Between 0 and 4) AS Past5DaySum FROM tblData;
Someone help me in modifying the above query ,so that ,a Type(1,2,3) can be assigned to each Past5DaySum based on the following criteria.Is,it possible to use IIF function for three variables ?,so as tp get a new table(Mydate,MyValue,PastDaySum,Type)
Hi there, is it at all possible to use variables in sql? Please consider the following snippet:
... (WHERE [DateDue] < Date())...
The idea is to return a recordset where the DateDue is prior to the current date. But sometimes I need the opposite, ie a recordset where DateDue is in the future: ... (WHERE [DateDue] > Date())...
Right now I have 2 long queries which are exactly the same, except for the operator < for the first case above and the operator > for the second. I of course did not want to repeat the queries so I tried:
Dim op As String op = Me.txtOp 'txtOp is invisible and gets its value (either "<" or ">" ) thru 'a button click ... (WHERE [DateDue] & op & Date())...
Can it work? what should I correct or do differently here? I might need this kind of trick for other routines too, so I'd like to avoid copying the same query, just for the operator change. I hope one of you seasoned sqlers can tell me more about it, I appreciate.
I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.
Here is the formula I have constructed that does not work.
ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))
I am trying to incorporate the following code into my code: Code:If Not QueryExists("Q_ABC") Then Set qdf = db.CreateQueryDef("Q_ABC") Else Set qdf = db.QueryDefs("Q_ABC") End If Difference is that I want to use a variable to provide the query name... Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists(queryname) Then Set qdf = db.CreateQueryDef(queryname) Else Set qdf = db.QueryDefs(queryname) End If but this does not work... but neither does this... Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists("queryname") Then Set qdf = db.CreateQueryDef("queryname") Else Set qdf = db.QueryDefs("queryname") End If nor does this... Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists queryname Then Set qdf = db.CreateQueryDef queryname Else Set qdf = db.QueryDefs queryname End If Can someone please show me how this is to be done?? I feel that it must be something simple... Thanks a lot (in advance)!
The following code recognizes the value of 'TheYear' after running the line of code (ie. "#11/13/2007#"). The problem is it doesn't seem to recognize it in the SQL, but rather asks for a value when I reach the SQL code line. How can I get it to substitute automatically? THANKS! DoCmd.SetWarnings False TheYear = "#11/13/" & Year(Now()) & "#" DoCmd.RunSQL "Update [Employees Trained List] SET [Employees Trained List].[This Years Training Date] = Null WHERE (([Employees Trained List].[This Years Training Date])< TheYear );" DoCmd.SetWarnings True
I am currently making a project as part of my Computing course. The project is a running diary, where users can log in and record their running times(Amongst other things). As part of the system i am going to include a calorie calculator, to do so i have to work out the BMI (Body Mass Index). To do this you have to divide the weight by the square of the height. The weight and height are stored in the tables. I am planning on creating a form for this conversion which will refer to a query. When the users log-in their ID is stored in a Public Variable called "UserID". What i would like to do is query the database for their height and weight using this variable, but i don't know how to go about doing this. Does anyone have any suggestions? Many Thanks
