Pass Value Between Forms


I want to pass a value between two forms. I created a text field in one of the forms and tried to access it using:

aVariable = frmSearch.txtQryName.Text

It's not working for me. Is this correct?

Thank you,


How To Pass Value Between Two Forms?

I have a big problem. I have form A which display list of user errors. I need to pass selected error from that form A to another form B which contain user data in main form and error data in subform (this is error which I want to pass from A).
Form B tells me which error was reported from particular user. Errors are predefined in separet Form (A).

I hope you will understand me. I need help, because I am losing my maind with this one.....

View Replies View Related

How To Pass A Company ID Thorughout All Forms


I have a database that controls donations for a Charity. There is to be a sister charity added to the database and a lot of the previously created objects can be used to provide for the new Charity.
My idea was to create a "company" ID and label the categories of the donations. I could then use this company ID to identify to whom the data captured belongs.

I have used the switchboard manager to enable navigation, in this I have a ONload event procedure that requests the Company ID (1 or 2).
That's as far as I have got.

How would I get Switchboard to open each form (some being based on queries - a couple direct on the tables) and filter the data on the company ID?

If there is a better way, please enlighten me!! :)

Many thanks in advance


View Replies View Related

Using Forms To Build Sql WHERE Statement For Pass-through Queries

Hi all, i'm currently working a MDB project that aims to develop a front end access solution for users of the sql database. The system is used only to made adjustments to the WHERE clause of the SQL pass-through statement. The SELECT and FROM statements are pre-determined and users won't need to update this. The result is a read only. You might be wondering why i don't use ADP instead, well one of my limitations is that i won't have write access to the database. They are afraid i will corrupt data...

I want to use a series of text and combo boxes to build my WHERE statement, which will be added to the main SQL statement and then pass-through to the server, making use of its much beta processing capabilities. However as i'm trying to read up on VBA, i still have very little knowledge on how this can be done. Its actually similar to the Filter By Form option in datasheet view when i open a pass-through query. However, conditions applied through here means the processing is done on the user's computer, through testing i found this to be unreliable as it causes access to hang frequently. I would love to have this filter by form view available up front to the user before he even executes the query. As i mentioned, he keys in the criteria/conditions and access builds an sql where statement and appends it to the main SQL statement.

any idea how to do this? how do i capture user input and make access construct a WHERE statement from it For example

textbox daterange
Enter range of dates in here: date 1 and date 2
the where statement would then be "WHERE date BETWEEN date1 AND date2

Or is there other more efficient alternatives? sorry i'm very new to VBA and form development, would appreciate lots of advice and answers.

Many Thanks

View Replies View Related

Forms Pass Values - Version: 2003 (11.0)

I have a customer sevice issue form with a an event button that opens a new form in add mode (macro).
I can pass the value by setting the value of the fields equal to the first form.

However How do I retain those values in the the form itself. Is there something I can do with the default value to retain this?

View Replies View Related

Procedure Works Great First Pass, But Not The Second Pass

Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.

View Replies View Related

Pass The Value

I have 2 forms

I made 1 textbox in one form, second textbox in another form. I want to pass one value in one textbox to another textbox of another form.

How can I write the code? Thanks.

Textbox: 200

//will appear the value
Textbox: 200

View Replies View Related

Pass A Parameter...

Hi there

When using parameters from one form to the next I normally hide the form and then reference the parameter textboxes in the next form to the hidden form. Is there a better way of doing this as I saw threads here mentioning passing a parameter. How do I do this? Thanks!


View Replies View Related

Pass Value To Scriptlet

Hi all...

i'm having trouble trying to pass a field value from a Data Access Page to a Scriptlet.

Om my DAP i have:

Function returnId()
Dim pId
pId = formName.Id.value
returnId = pId
End Function

And on my scriptlet i have something like this:

Function public_DropMenu()
Dim rId
rId = parent.returnId()

sqlStr = "SELECT * FROM tbl WHERE Id=" & rId

End Function

But parent.returnId() always returns an empty string... Is there any other way to do this?

Thanks in advance.

View Replies View Related

Pass-Through Query Help

I'm trying to create a PTQ and just cant seem to find the source table.

The name of the source table is PRM1_ORG_MTRX3_N

If I write my query like this, I get an error code that states username.PRM1_ORG_MTRX3_N is an undefined name. It adds my userid to the beginning of the table name.


PRM1_ORG_MTRX3_N as T128

T128.AS_OF_DATE like '1/1/2007'

Then I rewrote the query like this, and I got and error code that said PRM1.ORG_MTRX3_N is an undefined name. Does anyone know what else I can try here to get this query going? Thanks


PRM1.ORG_MTRX3_N as T128

T128.AS_OF_DATE like '1/1/2007'

View Replies View Related

Pass Thru Query

I am re-writing some SQLServer stored procs as Pass-thru query on Access 2003.In the SQLServer SP there was a reference to a UDF on the SQLServer.
When I try to run my query I get an error saying the UDF objject does not exist.I can run the UDF on Query Analyzer by referencing it using the owner ( my_dev.UDF )
Any suggestions?

View Replies View Related

Pass The Value To DataSheetView

How to pass the values from the first page to the second page? The first page contains the lists of Area_Code. After selecting the area_code and the button being clicked the second page automatically call the Query1 (where the area_code's Criteria is Forms!MainForm!SubForm!txtAreaCode) and get the data back on the DataSheetView on the second page.

I found many people use the comboBox and AfterUpdate() to requery the data. But my case is different. Because on the first page, I have a list of area code which has the button to check the authorisation before continuing to the second page.

Thank you very much!

View Replies View Related

Pass Parameters

I need some help please on passing parameters such
as a recordsource to a report but not using OpenArgs
I heard that this can be done using a hidden form.

I have a calling form (form1) which opens a preview snapshot
form (form2) which in turn brings up a rptCurrentRecord (form3)
I can't get the openargs RecordSource SQL from form1 to form3

For arguments sake, Form3 in this case is really the report itself.

Hope this is not too confusing.

Thanks for any help

View Replies View Related

Control Won't Pass

So I have a function (TradeRoof_AfterUpdate) inside my form (frmCustomers) that calls one of 2 functions (ActivateConditions or DeactivateConditions) in a module (modRender). ActivateConditions is then supposed to call on one of 2 functions, Activate or Deactivate, also inside modRender. None of these functions are supposed to return a value, just change visibility of a control and true to false or vice versa.

i don't know why, but for some reason, i can't pass a control from ActivateConditions to Activate or Deactivate. Here are the functions (DeactivateConditions and Deactivate are nearly identical to their counterparts):
Code: Public Sub ActivateConditions(Sticker1 As Control, Condition1 As Control, Optional Condition2 As Control, _Optional Condition3 As Control, Optional Condition4 As Control, Optional Sticker2 As Control) Sticker1.Visible = TrueIf Not IsMissing(Sticker2) ThenSticker2.Visible = TrueEnd IfIf IsMissing(Condition2) ThenActivate Condition1ElseIf IsMissing(Condition3) ThenActivate Condition1Activate Condition2ElseIf IsMissing(Condition4) ThenActivate Condition1Activate Condition2Activate Condition3ElseActivate Condition1Activate Condition2Activate Condition3Activate Condition4End IfEnd Sub

Code: Private Sub Activate(ActiveCondition As Control) ActiveCondition.Visible = TrueActiveCondition = FalseEnd Sub

when i hit the trigger on my form, i get this error: "Run-time error '91': Object variable or With block variable not set," and then it highlights "ActiveCondition.Visible = True" in my Activate function.

Why can't I perform this action? Any help is greatly appreciated!


View Replies View Related

Pass Through Queries

Is it possible within Access to use a pass through query that joins tables from two different servers?

If not I have a workaround solution though this is a more intensive effort.

As always, you are all great here and any help is appreciated.

View Replies View Related

Pass Through Query

Can you do a pass through Query to a pivot table when your query has parameters? I'm reading like you can't, but nothing has been said concretely yet. When I do it, it gives me an error "Trouble Obtaining Data" when I try and set the layout.

View Replies View Related

Run Pass Through Query


Sub RunPassthrough ()

' ==============================================

' Example code for RunPassThroughQuery ()

' ----------------------------------------------

' Runs a passthrough query to delete all records

' from the Customers table.

' ==============================================

Dim bOK As Boolean

Dim strDB As String

Dim strUser As String

Dim strPWD As String

Dim strDSN As String

Dim strSQL As String

strSQL = "DELETE * FROM Customers"

strDB = "Pubs"

strUser = "Ollie"

strPWD = "North"

strDSN = "Pinnochio"

bOK = RunPassThroughQuery (strSQL, strDB, strUser, strPWD, strDSN)

End Sub

Function RunPassThroughQuery (strSQL As String, strDatabase As String, strUID As String, strPWD As String, strDSN As String) As Integer

' Comments : runs the supplied sql string as a passthrough query to an ODBC data source

' Parameters: strSQL - SQL string to pass to the server

' strDatabase - name of ODBC database

' strUID - user name

' strPWD - password

' strDSN - data source name

' Returns : True if successful, False otherwise


Dim db As Database

Dim qdfTmp As QueryDef

Dim strConnect As String

On Error GoTo err_RunPassThroughQuery

Set db = CurrentDb()

strConnect = "ODBC;DSN=" & strDSN & ";DATABASE=" & strDatabase & ";UID=" & strUID & ";PWD=" & strPWD & ";"

' Create a temporary query to get hold the SQL string

Set qdfTmp = db.CreateQueryDef("")

' Point the query to the server

qdfTmp.Connect = strConnect

qdfTmp.SQL = strSQL

qdfTmp.ReturnsRecords = False

' Execute the query

qdfTmp.Execute dbSQLPassThrough


RunPassThroughQuery = True


Exit Function


RunPassThroughQuery = False

Resume exit_RunPassThroughQuery

End Function

View Replies View Related

Pass Arguments To Access


I want to be able to pass arguments to an access file on start up. I want to be able to grab this value and then perform an action based on this.

The program will send an email with details for a change request, the recipient will then need to click on a link to accept or reject this change. So I want them to be able to click the link which will trigger the program to save accept or reject.


View Replies View Related

How To Pass Data From One Table To The Next?

Hi everyone! :)

First off I have to say I'm not an Access Guru by any means. I can make a very very basic database in access but beyond that I'm toast.

So I've just started working on a project that is in it's second year. The project was started last year by a student and she created, what seems to me, to be an adequate small database for the project. I've noticed however that when entering data, some of it has to be entered twice. This has been made easier through the use of the look up tab in the field definition (?). However, I worked with a student before who created a database where the primary key value of a "parent?" table was automatically forwarded to the corresponding field in the "child?" table.

So here's a hypothetical, let's say you have a restaurant, and one restaurant can have ummmm many tables, and then one table can have manyyyy chairs! There's a unique restaurant id created in the restaurant table, which is say needed with a table number to create a unique table id in the table table, which is in turn needed with a chair number to create a unique chair id in the chair table? So you basically have 3 tables where unique ids are being created. So then lets say you have a chair description table that has fields in it to contain all the ids made previously, as well as some of the information entered to create the unique fields (i.e. table number, chair number). My question is..if I've already entered this data in previous parent tables then why is it that the data doesn't carry forward to these fields? Is it because these fields are not primary key fields in this last table? If that's the case then is there a way to set this up? Right now there's a select statement used in the look up reference option that has something to the effect "select table.table_name from table;". So I know what it's doing and when I enter data I can select the table id I need. It just seems a bit redundant data entry wise to enter yet again values I entered the table before, or values that were generated for me in the table before.

Is there a way to have these fields automatically populate with the corresponding data that was entered to get to this table? Does it involve modifying the select statement to involve a where clause (terminology? clause?). Any ideas would be very much appreciated! Thanks.


View Replies View Related

How To Pass Parameter To Query?


I have created a simple query that is used as the record source of one of my forms. I want to pass the query a different criterion each time the form is opened.

Any suggestion/example on who to do that will be very much appreciated.


View Replies View Related

SQL Pass Through Form Variables

I know this is probably obvious but how do I pass through form variables to sql server I currently have:

exec QStudent @_param_cmbYear="0708", @_param_SelID="S", @_param_SelForename="d", @_param_SelSurname="S"

this executes correctly SelID being a student ID or partial ID also allowing for surname forename partial search. I thought it would just be:

exec QStudent @_param_cmbYear=[Forms]![Attendance and Lateness Main]![cmbYear], @_param_SelID="S", @_param_SelForename="d", @_param_SelSurname="S"

to switch acad year to a form variable and repeat for the others but it errors on trying to save the pass through. Thanks for the help.

View Replies View Related

Pass Through Query Problem

I am using a Pass Through query to execute a Store Procedure on SQL Server 2000. The Store procedure checks whether the user is a member of the Domain administrators group. Unfortunatel, it returns a null value. If I set the store Procedure to check on any other domain role it works fine, so i know the code is correct.

I realise this is more of a SQL Server problem, but there may be help available from one of you guys out there.

In addition, can anyone advice of a good forum for SQL Server?


View Replies View Related

Pass-Through Query Question

I have an Access front-end connected to Oracle tables. When the user opens up the main form to add a new entry, I need to be able to pull the next sequential record number. However, I can't get this to happen, without filling out the entire record, saving, and then it will committ the number.

Can this be done with a pass-through query to the Oracle table, find the next sequential number, and then add it to my record id (ie, 06-1050, with 06 being the year and 1050 the next sequential number). I do have two fields in the Oracle table TXTYear and LNGNumber. This concatenated field is related to three additional tables that have more information. Each of these tables need to cascade a new record.

Confused? me too. any help would be appreciated.

View Replies View Related

Pass-Through Query Question

I have an Access front-end connected to Oracle tables. When the user opens up the main form to add a new entry, I need to be able to pull the next sequential record number. However, I can't get this to happen, without filling out the entire record, saving, and then it will committ the number.

Can this be done with a pass-through query to the Oracle table, find the next sequential number, and then add it to my record id (ie, 06-1050, with 06 being the year and 1050 the next sequential number). I do have two fields in the Oracle table TXTYear and LNGNumber. This concatenated field is related to three additional tables that have more information. Each of these tables need to cascade a new record.

Confused? me too. any help would be appreciated.

View Replies View Related

Can You Silently Pass A Parameter Without VBA?


Can Access handle paramater-passing without getting into the object model and adding a "Parameter" datatype to a query and calling DoCmd.RunSQL etc, and also without prompting the user?

Is something like this possible (as it is in SQL Server I believe):

SELECT * FROM some_query_that requires_a_parameter(param_value='china')

I am trying to avoid the annoying bit where Access Prompts you for a parameter with the little popup, and I'm also trying to avoid the little bit called "programming it with VBA".

I'd like to see if this can be done with only a SQL query in Access.

Any thoughts are appreciated!



View Replies View Related

Pass Date Value From Combobox

Hi all,
Dont know why im having such a hard time with this one. im trying to pass a date value from a combo to a query in my subform and failing miserably. thought it would be as simple as doing a


in the criteria for expensedate, but im getting no returns. what am i missing?
cbo expense date is formated as a shortdate and its values are from the expense date field itself.

Thanks in advance

View Replies View Related

Pass Values Between Subs

Not been doing this too long, but have a question. Have a sub that determines which checkbox(s) are selected. I then want to get the value from that sub and use it in another sub that runs a query to populate listboxes.

Code so far
Public Sub TransportMode()
If Me.checkAir = True Then
strtypes = strtypes & "'Air',"
End If
If Me.checkCar = True Then
strtypes = strtypes & "'Car Hire',"
End If
If Me.checkAll = True Then
strtypes = "'Air','Car Hire',"
End If
strtype = Left(strtypes, Len(strtypes) - 1)
MsgBox strtype
End Sub

Public Sub FromToUpdate()

strSQL = "SELECT DISTINCT MasterTable.From FROM MasterTable WHERE MasterTable.Type IN (" & strtype & ")"
If Len(strbgs) > 0 Then
strSQL = strSQL & " AND MasterTable.BGID IN (" & strbgs & ")"
End If
MsgBox strSQL

How ever when get the message box, the value from first sub is not used by the second. How can I call it across? I'm trying to put bits of code like the first sub into many different sub as they are used again when buttons are pressed on the form etc.


View Replies View Related

By Pass Validation Rule

I've the following validation code on a data entry form

Private Sub CboCommCode_Exit(Cancel As Integer)

If IsNull(Me.CboCommCode) Or Me.CboCommCode = "" Then
MsgBox "Community is required! Enter community or select from list!"
Cancel = True
End If

End Sub

after the user fills in the required info and press the add button, i clear the form and set the focus back to Me.CboCommCode for the user to add the next community. the problem i'm facing is when the user wants to quit and click the close button, the above validation rule kick in and ask for a community since i clear it and the user will have to enter something in the Me.CboCommCode then click the close button to exit. How can I tell Access if a close button is click, ignore the validation rule? Your help/suggestion is greatly appreciated.


View Replies View Related

Send Toa Pass Protected Db

I am exporting all my tables with the folloiwng function:
Public Function ExportAllTablesP(DBName As String, strPassword As String) As Boolean
' call it so : ExportAllTablesP "C:ee.mdb", "secret"
Dim FrontDB As Database, BackDB As Database, Tbl As DAO.TableDef
Set FrontDB = CurrentDb
Set BackDB = OpenDatabase(DBName, True, False, ";PWD=" & strPassword)
For Each Tbl In FrontDB.TableDefs
If Tbl.Attributes = 0 Then
DoCmd.TransferDatabase acExport, "Microsoft Access", BackDB.Name, acTable, Tbl.Name, Tbl.Name
End If
End Function

I need also to export to this database also certain forms and macros.For example:
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:BELest & Format(Date, "dd-mm-yyyy") & ".mdb", acForm, "FrmStock", "FrmStock"

Is there any similar function allowing this export ?

View Replies View Related

Open Form And Pass Data

Hi All,

I should know how to do this but my brain has gone blank.

I have a form "frmMainClient" which has a field "account_no".

On the main form "frmMainClient" I have a command button which opens a pop up form ""frmJobNew" where I can enter a new job record. The "frmJobNew" form has a field "account_no".

I am trying to auto fill the "account_no" field with the data from the "frmMainClient" - "account_no" field and store the passed data when the record is saved.

I can pass the data using "=forms![frmMainClient]![account_no] in the "account_no" field on the "frmJobNew" but when I save the record the data is not saved.

Can anyone offer help, please

Many thanks.

View Replies View Related

Pass Parameter Input To Excel

Please can someone tell me how to go about adding to the code below. Currently the user enters the date criteria in a form. This works great and the data is exported to excel. But I can't seem to figure out how to get the input from the user to also be exported to Cell A1 in excel. Example: if the user enters starting date and end date, that information should be placed in the A1field in excel. Thanks for your help.

Public Function ExportDataExcel()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String

If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then
Exit Function
End If

'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking"
strFileName = "Output.xls"
strFileTemplate = "Template.xls"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''

'This deletes the old file
Kill strFilePath & strFileName
'This recreates your file with the template
FileCopy strFilePath & strFileTemplate, strFilePath & strFileName

openexcel strFilePath & strFileName

ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED"
ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED"

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''

'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName

'Uncomment/Comment these to close out the workbook
DoCmd.Close acForm, "frmLar"
Set xl = Nothing

End Function

Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef

Application.SetOption "Show Status Bar", True

vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")

'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.
'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.

Set dbs = CurrentDb
'QueryDefs (0)
'QueryDefs ("name")

Set qd = dbs.QueryDefs("" & strQuery & "")

qd.Parameters![txtStartDate] = [Forms]![frmLar]![txtStartDate]
qd.Parameters![txtEndDate] = [Forms]![frmLar]![txtEndDate]

Set rs = qd.OpenRecordset

'Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record

'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
'There are 1 or more records. Now Select the sheet that you will be exporting to

'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'

'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook

'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.

xl.Cells(intR + 3, 1).Value = rs.Fields(0)
xl.Cells(intR + 3, 2).Value = rs.Fields(1)
xl.Cells(intR + 3, 3).Value = rs.Fields(2)
xl.Cells(intR + 3, 4).Value = rs.Fields(3)

'Moves to the next record
Next intR 'Loops back to For and enters data for the next row

'Once the export is done, this just puts the cursor to A1 on each sheet

'Clears the recordset
Set rs = Nothing

vStatusBar = SysCmd(acSysCmdClearStatus)

End If

End Function

View Replies View Related

Pass-through Query Causing An Error

I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".

The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!

Does anyone have any idea what is causing this?:confused:

View Replies View Related

Where Clause In A Pass Thorugh Query


Any hellp would be appericated.

I have a pass thorugh query that connets to a Oracle database with a where clause with a between start date and end date.

I need the start date and End Date to come from a form within MS Access.

Is this possible? if so how do I code the where clause to get the start and end date parameters from the form?

View Replies View Related

Copyrights 2005-15, All rights reserved