I'm attempting to pass a variable from form to form...I'm having trouble doing it...I checked the forum and read a little that the form I'm passing it from must stay open...is this correct? I have my variable as: Public strUserName as String. It does work fine if I leave the form open then I can pass it...
But I want to be able to close the form and still pass the variable...How can this be acheived?
Thanks in advance.
Kacy
how can i pass a variable from one form to another
this works fine
Tracking_Number = Forms("Main").Control("mytext")
but if "mytext" was in vb (rather than from a text box as shown above) how can i do this
Hi
I have a matrix variable (ex: test(7,3)) define as private in a form's code vba. In this form, I open a report in which I would like to show the values of my matrix variable. How can I do this efficienly ... what i use now is a public buffer variable in a module(it eats memory for nothing)
can someone tell me a trick ...
tanks a lot
In form1 I've a command button to open form2.
Code in form1:
Private Sub button_Click()
On Error GoTo Err_button_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "form2"
stLinkCriteria = button.Caption
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_button_Click:
Exit Sub
Err_button_Click:
MsgBox Err.Description
Resume Exit_button_Click
End Sub
button.Caption contains a valid date value
In form2 I want to display the date in a label.
Code in form2:
Private Sub Form_Load()
Dim DatePassed As Date
DatePassed = CDate(Me.OpenArgs)
label.Caption = DatePassed
End Sub
On executing I get an error like "Invalid use of null value."
Who helps?
I have a Form 01 that has staff names and the primary key StaffID. From this form, I want to open Form 02 to add a new record linked by the StaffID. How do I automatically populate the PK (StaffID) in Form 01 as the FK (StaffID) in Form 02?
View 1 Replies View RelatedI want to build an SQL statement in code which includes the value stored in a string variable where the variable name includes a loop counter.
This is a much simplified example of what I am trying to do:
Dim i as integer
dim Strtable1 as string
dim Strtable2 as string
dim Sqlstring as string
...
Strtable1 = "tblEmployees"
Strtable2 = "tblSales"
...
Sqlstring = " select * from ... where....."
for i = 1 to 10
DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring
Next i
I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.
Hi Everyone,
I'm trying to pass a variable called MyFilter between forms but am having problems. I have created a Module and declared MyFilter as a public string.
The original code in my first form is:
Private Sub Command65_Click()
Dim MyFilter As String
If Me.Filter = "" Then
MsgBox "Please apply a filter to the form first."
ElseIf Me.Dirty Then
' Make sure the record is saved
RunCommand acCmdSaveRecord
Else
MyFilter = Me.Filter
DoCmd.OpenReport "Temp", acViewPreview, , MyFilter
End If
End Sub
How to I change this so that it now stores the value in the Public variable instead of the Private one which it is doing above?
Thanks,
I have an event on a form kicked off by On Delete and I have an event on the same Form kicked off by After Delete Confirm. I need to pass a variable from the On Delete Event to the After Delete Confirm Event. I have set up the variable as public, but it keeps getting reset inbetween the two events. Any ideas?
View 1 Replies View RelatedI have a button on my main db, that opens a second db using hte following code:
----------------------
'Dealing with external objects, use inline error trapping
On Error Resume Next
Dim appAccess As Object
Dim db As Database
Dim strAppPathName As String
Dim strAppName As String
Dim strTimesheetPathName As String
[Code] ....
This code works great to open the other db, and handles wheter the other db is already open or not, but I cant seem to pass the variable to the other db using the startup switch /cmd.
I suspect if I used the shell method it would pass the cmd , but I havnt found any way to test if the db is already open with the shell method.
How can I pass a variable to the other db when opening it using VBA?
I have two tables and two separate forms. [ID] is the link between them. (I cannot do a subform in this case).
Form1 displays 3 records
Have control button that goes to Form2 where [id] = NumID (NumID is "long" variable)
Have control button on Form2 to go back to Form1. It needs to go to the same record it came from. I don't want use a where clause (e.g., where [id] = [id]). This works, but the form1 is now filtered. I need all records available in Form1, but I want to go to the record it was on when the button was clicked to go to form1
Example:
Form1, record1, ID=30
Form1,record2,ID = 35
Form1,record3,ID = 40
Select record3,ID=40, then click on button to go to form2 (has where clause where ID=40).
Form2 is filtered and is correct.
Click on button to go back to form1. No Filter. It goes to 1st record (record1,ID=30). I need all records displayed, but need to be on record2,ID=35.
Hi all!
I have a college project were we have had to code a database for a fictional hospital to hold patient, doctor, consultant and appointment information. I am struggling with one particular problem.
I am trying to pass a variable from a combobox of results to another form to display a certain record.
I am trying to send:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=" & "'" & AppointmentID & "'"
to the form but it fails, yet if I "hard code" the variable it works:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=23"
Can anybody shed any light upon this please?
I need to pass a variable from one Access Database to another
The scenario is I have a item number in one database that I need to use to open a form in another database,
I can open the database using vba, currently launching a BAT file that copies the latest version database to a local drive and then opens it,
Is there an easy way to pass the variable after this has completed?
I have a form with 5 buttons on it. Each button is meant to select a warehouse location, so a query can be run to give an inventory report for that location. There is a separate query for each button and the OnClick event does properly modify the recordsource to give the appropriate data to the report for the location selected.
To this functionality I want the OnClick event VBA to pass the warehouse location to a textbox on the report, so the title of the report reflects that inventory location.
My code thus far is:
Private Sub Command5_Click()
Dim mySQL As String
Dim WHSE As String
mySQL = "SELECT [Master Part List].[Part Number], [Master Part List].Category, [Master Part List].Description, [Master Part List].MaterialCost, [Master Part List].Inventory, [Master Part List].Update, [MaterialCost]*[Inventory] AS [Total Cost], [Master Part List].Warehouse"
[Code] ....
When I get the report, the textbox is empty, instead of containing the text value for the warehouse location.
I 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 trying to pass a boolean variable to a class module
Code:
Set rps.ViewS = View
the code in the module that this in theory is calling reads as
Code:
Private ViewC As Boolean
Public Property Set ViewS(ByRef ViewA As Boolean)
Set ViewC = ViewA
End Property
Public Property Get ViewS() As Boolean
Set ViewS = ViewC
End Property
However I am getting the error message
Quote:Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.
I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
View 4 Replies View RelatedWhat would the best way to open a series of queries, if you aren't sure how many of those queries there are? I have the following:
Code:
DoCmd.Close acReport, "SCEFReport"
DoCmd.OpenQuery "ClearTBLQuestions", acViewNormal, acEdit
DoCmd.OpenQuery "Q1append", acViewNormal, acEdit
DoCmd.OpenQuery "Q2append", acViewNormal, acEdit
DoCmd.OpenQuery "Q3append", acViewNormal, acEdit
Which continues as is for 10 or 11 queries named in a similar pattern, then prints a report. However, as the number of append queries is not set (they are generated in a different quantity for different purposes, it's complicated), I am not sure how to check how many there are with that style of name.
I have code that from DB1 opens DB2 and runs a Function in DB2.
The DB2 function produces a Boolean result that i am trying to get back to DB1 without success. Below is the script.
Sub Test22()
Dim AC As Object
Set AC = CreateObject("Access.Application")
rc = "K:ARSHRAutomation_ProjectsMikeFEDB2.accd b"
AC.OpenCurrentDatabase (rc)
AC.Visible = True
AC.Run "SendVariable"
End Sub
Because i am using AC.Run "SendVariable" i cant find a way to get the value of SendVariable!!
I am trying to set up something to be able to take me to a folder that holds various forms for personnel. I have a query that generates the link for each person. I have tried to set it up as a hyperlink in ACCESS 2013 and it displays as one but doesn't act like one.
I want to be able to click the link and have it open up a personnel folder for that individual. I can't find a MACRO that I can create to do it. Example of my query is that it creates a link K:Main BreakdownSection BreakdownPersonnel FoldersName and the name is the variable part.
My next problem will be to have it create that folder when we have new personnel arriving.
I would also note that I have not worked with ACCESS in many years and much of what I was able to do with 2002 and 2003 doesn't work with the newer versions of ACCESS.
I would like passing values from first form until third form.
In the first form I have a list box after selecting items (For each selected item in first form I have 4 values) and pressing button (or right click of mouse) the second form will be open, then in the second form I have 2 option (inserting, deleting), when I select inserting or deleting in the second form, third form will be open, in the third form there is a "OK" button, when I press that, passed values from first form will be used for inserting or deleting records to the table.
Hi All,
I'm not sure if this should go in here or in VB Programming, because it's to do with an Access Database but it's VBA code.
But I understand that VBA and VB are different in the way they work.
I think this may have been looked at before, but I'm not very good at using the search function. I've looked on google, but all I've seen doesn't seem to be for my use, so I came here as recommended by "RadioActive Frog" who I believe is a member here.
Right, basically, I am writing an Access database for my company's ordering system.
I have a form called "Enquiry" which has many tabs, one of these is the "client" tab in in here are many fields:
Firstname
Surname
Company
Addressline1
Addressline2
Town
County
Postcode
Phones
Fax
AltMobile
Email
ContactType
Now, there is an option to enter a new client or search the database for an existing client and it is basically the "new client" which I'm having issue with at the moment.
Basically, clicking the button "New Client" opens another form called "Client". Here, the user enters the data into fields which are the same as above (but on this contact form).
When done, they click a button called "Save and Close" which then asks the user if they want to paste their entered data into the original main "Enquiry" form.
It's this last bit, the transferring of this data I can't get my head around and would be incredibly greatful for help with.
My Code is below:
Code:Private Sub Save_Click()On Error GoTo Err_Save_Click ' On clicking save, a dialogue box will open asking if you want to paste this data ' into the enquiry form. Clicking yes will do this. Clicking no will just close the box 'declare intpress as an integer Dim SavePress As Integer 'Save Command DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'when save and paste is clicked, ask if the information should be pasted into the form or not SavePress = MsgBox("Would you like to paste this Contact Information into the Enquiry Form?", vbQuestion + vbYesNo, "Paste details") If SavePress = 6 Then 'If the enquiry form was the form which initiated the cotact form, then copy and paste 'the informtion into the enquiry form and the close the form 'If "Enquiry" = Me.OpenArgs Then <------ I have decided not to use this, as it doesn't seem to work. (I'm probably not using it correctly) Enquiry!FIRSTNAME = Me.FIRSTNAME Enquiry!SURNAME = Me.SURNAME Enquiry!COMPANY = Me.COMPANY Enquiry!CATEGORY = Me.CATEGORY Enquiry!ADDRESSLINE1 = Me.ADDRESSLINE1 Enquiry!ADDRESSLINE2 = Me.ADDRESSLINE2 Enquiry!TOWN = Me.TOWN Enquiry!COUNTY = Me.COUNTY Enquiry!POSTCODE = Me.POSTCODE Enquiry!PHONES = Me.PHONES Enquiry!ALTMOBILE = Me.ALTMOBILE Enquiry!EMAIL = Me.EMAIL DoCmd.Close acForm, "Contact" Else DoCmd.Close acForm, "Contact" End If Exit_Save_Click: Exit Sub
But It's not working. It's giving an error saying "Compile Error: Variable not defined" and it highlights the word which I've mad RED in the code above. Now, I tried changing the exclamation for a fullstop, and I also tried writing "Form.Enquiry.FIRSTNAME" (and also with exclamation marks). None have given a working result.
I'll try and get some print screens too for more visibleness (new made-up word there) so you can see what I mean.
Blessings,
Si
Edit: here are a couple of print screens:
1. The Client tab/page of the main enquiry form:
http://i6.photobucket.com/albums/y218/Mr_Si/Enquiry.jpg
2. The Client Details form, which opens as a result of pressing the "New Client" button in the main enquiry form (shown in the background):
http://i6.photobucket.com/albums/y218/Mr_Si/Client.jpg
Argh! I forgot I couldn't post links to URLs
Have one form that contains values AssociatedProject and AssociatedRelease that need to be passed onto another form that opens with a new record. Have tried different variations based on what I read here and couldn't get them to work.
Initial form - frm_ViewList contains the values that I need to pass on and has a "Add" button to bring up the new form that also creates a new record. The add button contains the following:
Dim stDocName As String
MyAssociatedProject = Me.AssociatedProject
MyAssociatedRelease = Me.AssociatedRelease
stDocName = "Frm:ManageQuestionsAnswersProc"
DoCmd.OpenForm stDocName, acNormal
DoCmd.GoToRecord , , acNewRec
Then in the second form Frm:ManageQuestionsAnswersProc the following code is contained in the Before Insert:
Me.AssociatedProject = MyAssociatedProject
Me.AssociatedRelease = MyAssociatedRelease
Hello,
I have a combo box on a main form and one on a subForm (DataSheet View)...
I want the value in the main_combo to populate the sub_combo for every record in the subform.
I think the code would look like this, but I cant get it to work?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub MAIN_DUNS_AfterUpdate(Cancel As Integer)
Me.CLIENT_DEAL.SUB_DUNS = Me.MAIN_DUNS.Value
End Sub
MAIN_DUNS= Combo on main form
SUB_DUNS= Combo on sub form
CLIENT_DEAL= Sub Form
DEAL_INFORMATION=Main Form
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any Ideas on how to get this to work correctly??
Hello,
I have made one form for report purpose. When I enter Start date and End date, I get report of the date range. What I am looking that I want to print the start date and end date in my report. How can I reference date text box in my report?
thanks
mithani
i have two forms. one is the main form and there i put button that opened another form.
when i open the second form i want to link the ID number from the main form to the second form so i wont have to insert the number each time i am opening it.
Hi all,
I have a continuous form that is bound to a SQL Server view.
For each record in my form I have a button, which when pressed opens up a second form. The second form is bound to a stored procedure that takes a parameter. The parameter value that I want to pass to this second form is the value of one of the fields in the first form.
I did the following in the click event of the button on my first form:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SecondForm"
stLinkCriteria = "[Field1]=" & "'" & Me![Field1] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
However, when I run this it keeps prompting me to specify the value of Field1 so this value is obviously not getting through. Do you have any idea why this might be happening?
Thanks in advance
Kabir