How to use a tempvar in the following line :
Forms!ColdTemperatures![TempVars("Button1")].ForeColor = vbRed
What I am doing is on close of a form I want to change the colour of a button on another open form (the name of the button is held in the tempvar). But I cant get it to work...
I have a table which is formatted as shown:
ID, My_Var, My_Value, Notes
This table holds variables that I want to declare to use throughout my application. I have been told in another thread that tempVars are the best way to do this.
I have written the following code, which works on a limited basis:
Private Sub btnSetVAr_click()
TempVars.Add "udvVar", Me!My_value.Value
End Sub
This defines a single variable on each button press, fine to work out how the code works, but not much use. What I really need to do is when the initial menu screen loads to call a routine to assign all the variables stored in the table using a loop to do this. The idea is to make all variables values easy to edit or add to, rather than have to edit code each time we need to change them.
The variables table holds 14 records so far, such as:
My_Var, My_Value
EuroRate, 0.885
ConDisc, 0.9
MollDisc, 0.8
As well as holding numbers, they hold strings and date values.
So, on loading the initial form, use an event to assign the variables from the table using the tempVar name as the value held in "My_Var" and it's value as held in "My_Value"
I am modifing the Inventory templete from Microsoft. A form is displayed (inventory transaction list) and you can select a filter for the data. Also on that form you can select a report. I would like to have the report respect the filter. I can see that the filter string is being stashed in a TempVar but haven't yet figgured out how to apply it to the selected report. Any insights would greatly appreciated
Thanks
I'm using Access 2007 and have a query in which I have a Where clause that includes a TempVars variable. Here is the SQL:
Code:
SELECT tbl_CostCenter.*, tbl_OpEx.*, qry_OpExByCC.*
FROM (tbl_CostCenter INNER JOIN tbl_OpEx ON tbl_CostCenter.[Cost Center] = tbl_OpEx.[Cost Center]) INNER JOIN qry_OpExByCC ON tbl_CostCenter.[Cost Center] = qry_OpExByCC.[Cost Center]
WHERE tbl_OpEx.[Base Year]=TempVars!CurrentBaseYear
ORDER BY tbl_CostCenter.[Cost Center];
I get runtime error 3070 stating that "The Microsoft Access database engine does not recognize 'TempVars!CurrentBaseYear' as a valid field name or expression." When I run the query without the Where clause, it runs fine, except it includes all years, not just the one I need.
However, when I use the immediate window to check the value of TempVars!CurrentBaseYear, I get a result of 2012 which is what I expected. So how can it not be recognized in the query? I thought that one of the perks of using a TempVars variable is that it can be used in queries.
MS Access 2010 accdb - Using TempVars open forms either for New Record or Edit Record.
Two sets of forms - One set works perfect but the other will not accept the criteria for New Record
MsgBox checks confirm the Variables are still valid in the 2nd form Open Event yet the If Then does not work.
Just before the If Then, an invalid use of Null message appears.
Access 2007 Sub-Report "rptSubEmployeeProject" inside report "rptProgressReportDay".
I need to dynamically change the table in the sub-report's record source. I tried (line wrapped in code tags below for reading purposes)
Code:
SELECT tblProjectHistory_fldProjectID,
FirstOfHistory, [History Date], [Time Spent],
Employee, fldAssigned, TheFieldPriority,
fldTitle, employeeID, fldTimeSpent,
fldStatus, fldHistoryID, fldOrder
FROM " & [TempVars]![TempEmpTempTable] & "
ORDER BY fldOrder;
And I get the error of invalid bracketing of name and it refers to the [TempVars]![Temp part. Makes me believe that I cannot use TempVars in a Reports RecordSource, is that accurate? If So that leaves me trying to set a sub-reports recordsource via vba right?
I’m new to Access and have been looking through a lot of the sample db,s very helpful, am I right in my conclusion that there is a lot of work being carried out in VB in the modules.
I’m reading all sorts of books at the moment and they are not going any ware near this area.
Is this the new 2003 program getting to grips:)
Hello,
I am getting an error with the Me function if present in modules. Is this because a module is Public?
How can I fix this problem and make the code run in a module?
Thank you.
This code runs an append query before closing a form named form1 and open another form named form2 using the c/n as linking criteria.
I am getting the error with Me.
I am trying to run this module from a menu bar.
thank you for you help.
Dim mydb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String
Set mydb = CurrentDb
Set MyRs = mydb.OpenRecordset("list")
stDocName = "form1"
stLinkCriteria = "[c/n]=" & "'" & Me![C/N] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenQuery ("AppendHISTORY")
DoCmd.Close
DoCmd.OpenForm ("form1")
End Sub
Sorry I have to ask such a simple question. I haven't worked on Access for over 10 years.
How do I find out which modules is the startup? Thanks.
DanYeung
Hi guys, im working on a project whereby a Visual Basic module has been designed in MS Access to email out a RTF document attachment of reports to shift managers when they have hazard items not closed in their area. Im building an ASP front-end to the database and would like to incorporate a button to do the mailout, however i dont know how i can link it to fire the code when the button is pressed. Is it even possible?
Nath
Friends,
I need to place a vba code from a regular event in a form to a module so that when the form opens the function in the module is recalled using a macro.
How do I do this?
For example, I have the following code in the OnOpen event of a form:
Dim strInputBox As String, bytChoice As Byte
InputPoint:
strInputBox = InputBox("Please input the password.", "Password Required - Restricted Area! ")
If Len(strInputBox) > 0 Then
If strInputBox <> "***" Then
bytChoice = MsgBox("Wrong password" & vbNewLine & vbNewLine & "Do you want to try another?", vbExclamation + vbYesNo, "Warning")
If bytChoice = vbYes Then
GoTo InputPoint
End If
Else
DoCmd.OpenForm "Maintenance"
End If
End If
I would now like to place this code in a module and run it using a macro.
Any help? Thanks.
I have form called frmplant on open I select plant type from the combo box then there is a sub form called plantitem tbl subform this show's the plant under that type on a continuous form so the user enters the plant information then they have command button to open another form call frmpopupPlantService to log the service history of the plant item the user enters the service date and service notes I have text box call "add" this shows the next service due this is the value I want to add to plantitem tbl subform in the service due date box when frmpopupPlantService form is closed . This what I have tried
Code:
Me.add = [Forms]![frmplant]![PlantItemTbl subform]![ServiceDueDate]
[Forms]![frmplant].Requery
But I get error
run time error
you can't assign this value to a object
Here a sample of my database : shanetest.zip
I want to know the day name of a date. I am getting type mismatch 13 for this line
PrintingDateDayName = WeekdayName(Weekday(PrintingDate))
So this code is not right. This is my full code
Dim RegisterDate As Date
Dim PrintingDate As Date
Dim PrintingDateDayName As Date
RegisterDate = Me.txt_register_date
PrintingDate = RegisterDate + 180
PrintingDateDayName = WeekdayName(Weekday(PrintingDate))
If PrintingDateDayName = vbSunday Then
MSG1 = MsgBox("It's Sunday", vbOKOnly + vbInformation + vbMsgBoxRtlReading, "Clients Database")
Else
MSG1 = MsgBox("It's Not Sunday", vbOKOnly + vbInformation + vbMsgBoxRtlReading, "Clients Database")
End If
I got this code to run correctly, which pulls records where they are not junk(can be seen at end of where condition), the only issue is that the "AND" does not turn blue. Is this a problem?
Code:
If Not IsNull(Me.txt_last_rec_id) Then
'DoCmd.SetWarnings = False
SQLText = "INSERT INTO tbl_batt_id " & _
"(record_num, bat_id, manufacture, date_code, barcode, status) " & _
"SELECT " & Me.record_num & ", bat_id , manufacture, Date_Code, barcode, Status " & _
"FROM tbl_batt_id " & _
"WHERE record_num = " & Me.txt_last_rec_id & " And [Status] <> 'Junk'"
[code]....
I built a query "WplatyKlienci" which sum all of payments "Sumawplat" from my clients "klient".
Code:
SELECT SUM(tblWycenaZakonczone.cenanetto) AS Sumawplat, tblKlienci.nazwaSkrocona AS klient
FROM tblWycenaZakonczone INNER JOIN (tblZleceniaZakonczone INNER JOIN TblKlienci ON tblZleceniaZakonczone.id_k = tblKlienci.id_k) ON tblWycenaZakonczone.id_zlecenia = tblZleceniaZakonczone.id_zlecenia
WHERE tblWycenaZakonczone.cenanetto is not null
GROUP BY tblKlienci.nazwaskrocona;
Now I need to know 2 things and I have problem with counting in sql:/
1) what percentage of the total amount represents a particular client.
2) I want to create a graph, so I need to do another query, which shows values form no. 1 only for 10 clients with the largest payments + one position named "others" which represents rest of clients
I know it can be done with task scheduling for a set time. However, I am looking for a way whereby the changing the time to shut off is very quick and easy to do as it would be continually done at different times. I have searched Google high and low but to no avail. I was hoping that I could have some VBA run with the On Timer event. The shut down would have to be done while the computer was either streaming a video or playing a radio station.
One simple way I could do it would be to pull the battery from a laptop and then have it plugged into one of those plugs that you just wind back for 60 minutes or 2 hours etc. before they shut off the power. However, would doing that corrupt Windows 8.1?
If I set a variable inside of a subroutine, it is set to nothing upon the end of the sub. Can I set a variable outside a sub and set its value, so that you can use it within subs?
View 14 Replies View RelatedI am making a database that will run the administrative part of a government run foreign teacher recruiting division.Running Office 2013 (and yes, it is overly bright), have used 2003,2007, 2010...I need to do mail merges, with lots of different sql's and templates. I have been using Albert Kallal's method and it works well.
However, some of my data is in THAI. And (I think) because this method exports to a .txt file first (.888) the data gets lost and shows as question marks.
Manual merge works fine.if I could export to xls in stead of .txt and use that as my merge source, that would work fine, but what part of the code would have to be changed.
Basically:
- export to xls in stead of the .888 file
- use the xls file as a datasource for the merge
I've got a bit of a problem with determining the max value in the table. I have a table called "Functions" and a column: "Function_KSW". There are values as follows:
281A01
421A01
281A02
etc.
I want to get the max value of them but under the condition that first three chars are e.g 281 so the max function will take only max from a narrowed criterion. The chars are to be selected from combolist in a form. I have written something like this:
Code:
Private Sub Click1()
Dim max As String
max = DMax("Function_KSW", "Functions", "Function_KSW like " * 281 * "") // here 281 for the test's need
// should be: like " * [Forms]![Form1]![ListBox0]*"?
[Code]....
BTW. When I want to find the max by the SQL in the query and based on the value to sort in combolist in the form, sorting doesn't work as well. When the dirtect value (here 281) is written in the code it is working. Seems that those ** stars unable to retrive the vaule from the combolist
I'm trying to customise the error message for Input Mask Violations, so that it references the control where the error has occured, rather than just being a generic error message.
I can make this work using the code below:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const INPUTMASK_VIOLATION = 2279
If DataErr = INPUTMASK_VIOLATION Then
MsgBox "Please check that the information you have added in the " + Screen.ActiveControl.Name + " field is in the appropriate format."
Response = acDataErrContinue
End If
End Sub
BUT, all my field names are shortened e.g. Mobile Number = MobileNo, so I want to use the caption property instead. I assumed I could just change it to "Screen.ActiveControl.Caption". But this doesn't work at all!
I have the following that I found in a post on Keypress of a text field to limit the field to only numbers. This works great only I need to disallow the entry of zero.
If KeyAscii <> 8 Then ' Not Backspace (important for error correction)
If KeyAscii < 48 Or KeyAscii > 57 Then 'Not a number
Beep 'Let the user know they hit an illegal key
KeyAscii = 0 'Don't let the keystroke through
End If
End If
I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.
Here is my code.
trimmed_department = Trim(Replace(new_department, vbCrLf, ""))
Example value being "123 Point 5 Finance and Accounting"
I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.
I have two subforms: sf1 (Based on Table 1) and SF2 (Based on Table 2) in the first I have the fields and CODE QTY.
I need to insert into the second table, as many records as QTY. to enter a serial number for each unit of the item. For example:
TABLE 1
COD. QTY.
001 2
006 1
I must insert into Table 2
001
001
006
Dim num As Integer, contador As Integer
contador = 0
num = Forms![FacturasRec_NSF]![FacturasRec_Det_SF].Form![CANLFR].Value
Do While contador = num
Insertar
contador = contador + 1
[Code] ....
but I could not even.
How can I do a progress in bar in a do while. I export contract and during the export i want to have a progress bar.
do while
msgbox "the export runs", progress bar
Loop
I am having some issues trying to use both and & or in a If statement.
The code below is what I am trying to do and it is not working.
Code:
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Or (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "No longer eligible") Then
MsgBox "Please enter Associate Name"
Me.Date_authorization_expires.SetFocus
I tried the code below and it works but as soon as a I enter the Or part it will not work.
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Then
MsgBox "Please enter Associate Name"
Me.Date_authorization_expires.SetFocus
I am not sure where I am going wrong.
I have the following code.
Code:
Dim ctl As Control
For Each ctl In Me
Me.Assigned = Me.Combo55
DoCmd.RefreshRecord
DoCmd.GotoRecord , , acNext
Next ctl
I keep getting an error that says cannot go to the specified record. How can I go about fixing this?