It just occurred to me that I use certain variables in several of my routines. Maybe you've done the same... something like:
Dim db as DAO.Database
Dim rst as DAO.recordset
Dim qdf as DAO.QueryDef
Now, if it's re-used in many routines, wouldn't it make more sense to make it a public variable, and set them to whatever you need it for a certain routine? Would it be more efficient than having to re-initializing and discarding it for every routine that used it? What problem would one expect from making them public?
Also, since it's very common to set db = CurrentDb, would it also make sense to set it publicly, and for cases where you aren't using CurrentDb, use a private variable just for that one?
I'm into one of the subforms that will be using the public variable from the main form and am not having success passing the data from the main form over. on the subform, i created a textbox (txt_currentyear) w/ this in the control source field: =[WrkYear] & " Golf Outing"
WrkYear was the defined in a module as: ********** Option Compare Database Option Explicit
Public WrkYear As String **********
I have created a listbox w/ a few years in it (current_year_listbox). this has =[WrkYear]=Me.Current_Year_listbox.Value in the after update field. i loaded the main form, selected the year, went into the subform to see if it passed the data along w/ no luck.
do i need to call or reference that module in every form or report before i can use the data from it? right now, i just get an empty field on the subform.
Hi all, I seem to have encountered a strange problem at work.
I declared a public variable for user department. The user selects his department from a Log On form and if the password is correct, he opens another form with the department variable passed on in openargs.
All of this works fine in access2k but upon testing with 2003, the openargs somehow do not seem to be being passed on. Maybe I am being dense somewhere but it is kinda puzzling for me.
I am not using access's security for users and groups because I am trying to reduce the complexity of the whole project (I wont be around to maintain it next time).
Is there any way of opening recordsets as public or global in a form? E.g. if i do something like
Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)
the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.
I would like to declare Global or Public variables from a table so they can be added to or edited easily. I had the following function to do this with Alpha Five but at the moment my lack of knowledge of Access VBA is making this task difficult.
Here is the function that I use in Alpha:
FUNCTION udVars AS A (udTabName AS C ) dim codeStr as c t=table.open_session(udTabName,file_ro_shared) t.fetch_first() while .not. t.fetch_eof()
[Code] ....
It opens a table reads in the records and then makes them into a string the string would look something like this:
"Public EuroRate as Single = 0.885"
I would then need to use this string to declare the variables but not sure what command to use - I was looking at the Eval function last night but couldn't get this to work...
The table would hold strings for all the above so you would have:
My_Var (variable name) My_Typ (type of variable e.g. Single) My_Val (value e.g. 0.885)
The table would hold as many variables as required, some would be dates, some paths for making directories and some would be numbers.
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database Option Explicit Public Function getCurrentRepDates() As Date Dim dtBegDate As Date dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
Access 2010 vba - I'm trying to pass a start date and end date to a date field in a make table query, and use the 'between' operator on that date field.
So I have a criteria on the date field like this "Between [dtStart] and [dtEnd]" and if I run the query manually it asks for 2 values and then works fine.
Here's the code I'm trying to run:-
Set qdef = db.QueryDefs("qryTest") qdef.Parameters("dtStart") = StartDate1 qdef.Parameters("dtEnd") = EndDate1 Set rs1 = qdef.OpenRecordset(dbOpenDynaset, dbSeeChanges)
and I get the error "3219 Invalid Operation" on the last line.
I am trying to add a common dialog to my form from the toolbox and get the following error 'you dont have the license required to use this ActiveX control. Is there any way I can sort this out? I am using Access 2002. I am getting desperate and any help would be much appreciated
I did a search on common access errors and other errors that you would want to trap and got the following results:
Cannot update. Database or object is read-only Operation must use an updateable query General error Unable to open registry key Could not find file Could not use '(unknown)'; file already in use Table 'tblTable' is exclusively locked by user 'Admin' on machine 'MyMachine' Too few parameters. Expected 1 Either BOF or EOF is True, or the current record has been deleted Item cannot be found in the collection corresponding to the requested name or ordinal The search key was not found in any record
If you know any extra errors, can you please add them to this list?
Is there a listing anywhere online for common regular expressions for validations rules? In other words, is there something that shows how to construct validation rules for things like phone numbers, addresses, postal/zip codes, serial numbers, math equations, etc. Two in particular that I'm looking for are phone numbers and postal/zip codes. Every time I try to wrap my head around the formula for a phone number (with area codes, an optional 1 in the begining, making sure no leading zeroes, making brackets around area codes optional, etc.) I only get a headache :mad: . I'll get it eventually, but a quick reference in the mean time and for future use would be very helpful.
I'm new to VB and am learing slowly with all your great help in this forum.
I'm wondering if its possible to write a Public Function for vbCRLF. I have a field in multiple forms that are all named 'Note'. If I have the following code:
Public Function PubNoteLF() On Error GoTo PubNoteLF_Err
My!Note = My!Note & vbCrLf
PubNoteLF_Exit Exit Function
PubNoteLF_Err MsgBox Error$ Resume PubNoteLF_Exit
End Function
When I run the code I get Compile Error: 'Sub or function not defined.'
Can someone tell me what I'm doing wrong? Its problably something real basic, but I'm stumped. Thanks.
I have to tables. One named wire and the other named Input. As my title says there is not unique ID for the tables to relate.. the only thing to relate is one ID where is not unique for all the rows
In the Wire Table I have 3 rows using the same ID, lets say 123. In the Input Table I have 1 row using the same ID as above, i.e. 123
I need to select a couple of fields from Wire Table and 1 field from Input Table The only relationship I have is the ID 123.... since the ID in Wire Table is repeated in 3 rows.... I would like to use the same value from the Input Table to be the same for the 3 rows in the other table.. Below is an example:
Wire Table:
ID Group 123 A 123 B 123 C
Input Table ID Description 123 Analog
My wanted result would be
Group Description A Analog B Analog C Analog
I tried using the Left join or Join function, but somehow the resulting table has many duplicates of the rows...such as:
Group Description A Analog A Analog A Analog A Analog A Analog B Analog B Analog B Analog B Analog ...
Any Idea on whats going on and how can i solve this problem????
I have my form where each records image displays as you scroll through the records. I want to add a control similar to the file dialog control, where users can browse to an image file on their machine, and then preview it and Update it.
Is there anyway to put common dialog box in MS Access Form. There is an option from ActiveX controls icon, but gives an error message, saying I do not have licence to use it ?
Hi all, I am new to microsoft access. I've got one question that can't solve. I am making database with several items. Under each item, there got different field lists to fill in. So I made several forms for each of them. In main form, I put one combo box to choose the item and one common button. So how can I write the code to open the several different forms,one at a time, by clicking one button according to the value that is chosen from combo box?? thank in advance.
Hi all, I have done a multi user database for a private company (and thanks to all those that helped me out) but now I need to do another database to put onto the Internet so the general public can view it and order the products. Obviously some of the database needs to be private (so I can update records) but some needs to be viewed over the Internet. I would like the general public to be able to view the products over a web page, not download a program to view the database.
I have made the database already so I can pull up a web page (from within access) and order the products - all I need to know is how to do this over the Internet?
I don't expect anyone to hold my hand through this, I just ask if anyone has a link to a walkthrough so I can do this?
Regards, James
PS Have another question - I have 17, 500 records and I wish to add a new Field, how can I change the values of the Field without going thorugh them one by one? e.g If the Record has 'Rarity = U' then I want the 'price' (which is the new field) to equal 1.
I haev a report with 15 sub reports. I have to use Visual Basic to format the sub reports. I know how to do the formatting (see below) but what I would like to do is have one function (Public) that I can pass the subReport name to and have the formatting happen. This would be mush easier to maintain than copying the code below into on_print events of all 15 sub reports.
Has anyone done this and have an example??
Thanks
-------------------------------------------------------------------------------------- Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer) Dim widthOfBox, startLeftSide startLeftSide = 0.017 widthOfBox = 0.21 Me.ScaleMode = 1 Me.ForeColor = 0 'Repeat the following line of code for each vertical line ' 1*1440 represents 1 inch
' Me.Line (0 * 1440, 0)-(0 * 1440, 14400) 'Draws line at Left Margin Me.Line ((startLeftSide * 1440) + widthOfBox * 0 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 0 * 1440, 14400) 'Draws line at Left Margin Me.Line ((startLeftSide * 1440) + widthOfBox * 1 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 1 * 1440, 14400) ' Draws next line Me.Line ((startLeftSide * 1440) + widthOfBox * 2 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 2 * 1440, 14400) ' Draws next line Me.Line ((startLeftSide * 1440) + widthOfBox * 3 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 3 * 1440, 14400) ' Draws next line Me.Line ((startLeftSide * 1440) + widthOfBox * 4 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 4 * 1440, 14400) ' Draws next line 'the 14400 is an arbitrary number to increase the line to the max of a 'section. End Sub
I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String Public Function returnName() As String If IsNull(gbl_loginName) Then returnName = "test" ' dummy account created for development only Else returnName = gbl_loginName End If End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name], [Entry of Hours].[Set Up Time], [Entry of Hours].[Run time], [Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date], [Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?
Code: Select distinct [tbl_DTP/CTP].id_zlecenia, [tbl_DTP/CTP].Folia_na_lakier_UV, [tbl_DTP/CTP].Wykrojnik, [tbl_DTP/CTP].Makieta, [tbl_DTP/CTP].Matryca_do_tloczenia, [tbl_DTP/CTP].Matryca_do_zlocenia, [tbl_DTP/CTP].kalka, tblGoraZleceniaRoboczeLaczenie.NumerZlecenia from [tbl_DTP/CTP] inner join tblGoraZleceniaRoboczeLaczenie on [tbl_DTP/CTP].id_zlecenia = tblGoraZleceniaRoboczeLaczenie.NumerZlecenia where tblGoraZleceniaRoboczeLaczenie.NumerZlecenia = r1;
Where "r1" is a public variable as string.The variable has value e.g. "10/145" But query can't get this value and all the time ask about value from "r1" :/
I have two tables of titles (DVDs and CDs). Each table has a price associated with each title in an adjacent column. I would like to match the titles between them and compare prices. Any help would be greatly appreciated. Thanks. EDS
Firstly I would like to state that i have very little knowledge of Access databases (my job is not computer related) but I am trying my best to get a few issues sorted... So here it goes: I have 2 tables, one large table with about 3-4000 rows(horizontal lines), and a smaller table with only about 1000 rows.
The rows in the big table contain Products (name, dates, adresses, phone numbers etc) and every product has a 'Codename' in a column-line (vertical) of the big table. (there are about 3000-4000 products) In the small table there exists a similar column-line (vertical) that contains 'Codenames' of the Products listed in the big table, but there are only ~ 1000 'Codenames' not the full 4000 ! The reason for this is that in the small table there exists another column-line(vertical) that for every code listed (in the small table) contains a certain specific "description".
My task is to get all the 1000 "descriptions" from the small table inserted into the Big table accordingly to their proper 'Codename'. This means createing a new column in the big table and: if the codename exists in small-table insert the description in the new column-cell, if the codename doesnt exist in small-table leave new-cell empty...
How do i do this ? :) Im guessing some coding is envolved.. and i have no clue whatsoever.... Thank you, George P.
