Accessing variable in one form from another Results

*frm1 has a Public string variable str1 and has a button that launches frm2
*frm2 is 'modal' and 'Pop-up', has a textbox 'txt1' and a button 'btnClose' that closes the form and sets 'str1' to txt1's value

If I launch Form2, enter something in the textbox, then hit the button, and try executing '!Forms!frm1!str1 = txt1' from within the btnClose's subroutine, then MS Access will tell me that it cannot find 'str1'.

I only see 2 courses of action here:
1) create a table to store this value
2) use an invisible form control like a textbox or label in frm1 (because something like 'Forms!frm1!invisibleLabel.caption = txt1' would work 100%)

Both of the solutions are awkward and I'm hoping you can help me come up with something better.

Is it possible to access variables in a form other than the one you are currenlty on.

What i want to do is have two buttons. View records and edit records. i an using the same form to do the viewing/editing. I have a boolean vaiable called edit_mode which will dictate what appears on the screen depening on it's value. This form is opened from the menu screen via Edit Record and vew record. When i click on edit record i want to set variable edit_mode to True

Hello hello!

What I want to do is:

Insert an image in a form into a bound object frame, then click a button and another form to open containing that image.

What I have already done:

I have created a module where I declare the bound object frame:

	Option Compare Database

' Access global variables definition

Global GBL_Photo As BoundObjectFrame

Option Explicit

Public Sub Init_Globals()

' Access global variable initialization

End Sub

Then in the form where the image is inserted, on the onClick event of cmdprint button the next happens:

	Private Sub cmdprint_Click()
On Error GoTo cmdprint_Click_Err
    GBL_Photo = chphoto.Value
    DoCmd.Close , ""
    DoCmd.OpenForm "frmPrint", acNormal, "", "", , acNormal

    Exit Sub

    MsgBox Error$
    Resume cmdprint_Click_Exit

End Sub

The frmPrint form opens:

	Private Sub Form_Load()

    OLEBoundprintphoto.Value = GBL_Photo

End Sub


When I run it the following message appears: "Object variable or With block variable not set"
Obviously the global variable GBL_Photo needs to be initialized in the Public Sub Init_Globals of the module but how?
Like: GBL_Photo = "/image.jpg" ? I get the same error!

Also the .Value property is the right property to transfer an image of a bound object frame to a variable?

Thnx in advance!

Hi All,
I am new to MS Access, so although I am comfortable with programming concepts, I do not know how to make the contents of a public variable in one form available for use in another form.

The environments I am used to allow me to set a public variable, fill it with some data and then access that from almost anywhere in the application.

Do I need to specifically pass the parameter to the next form? If so, how?


I'm new at Access and VB, so I need some help.

On a form (called LOGIN), I ask the user for thier name and password.

Then using VB, I open an Excel database that contains user info and confirm the password.

Also contained in that Excel file is their "supervisors name". Lets assign it MGR$ for now.

If I close the Excel file and the VB script, can I get at this variable MGR$ several forms later?

I hope this all makes sense.

Thanks in advance!!!


This must be easy but I am an old DataEase hand trying to make the great leap into Access and visual basic.

I have a form with an input box on it. Users type in a number and then click a button. The code behind this click action makes the input typed into a variable value. It then does some comparisons of this variable value to other tables. If it passes the comparison a new form is opened. What I want is the value of the first validated input to be immediatley posted to a field in this second form so that the user does not have to retype it (and therefore make all the previous validation useless). My attempts so far have not been very good as it does not seem to like passing this value from one form to another

Hi everyone,

I'm new to the world of Access and VBA. Have an excellent book, use VBA help and have scoured the web for a solution to my problems, but no joy so far.

What I want to do is pretty simple. It's just a look-up and selection of an address from one form to another. I have 2 seperate problems. The first is that opening the second form as modal with a selection of addresses displayed based on the post code entered just doesn't work. If the filtered selection works then the form isn't modal. If I get it to work as a modal form then the filter doesn't work. I've tried every possible permutation of ,,, etc. in the parameters of the DoCmd.OpenForm command.

The second problem is getting the selected address back to the first form.

Form A - user enters a post code then presses a button. When they press the button it should open Form B as a multi-record form with a list of addresses which match the post code entered.

Here's the code I currently have on the button which works to open the form as modal but doesn't apply the selection of records matching the post code:

Private Sub cmd_LookupPostCode_Click()
On Error GoTo Err_cmd_LookupPostCode_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_SelectStockAddress"

stLinkCriteria = "[PostCode]=" & "'" & Me![PostCodeLookup] & "'"
DoCmd.OpenForm stDocName, , , , , acDialog, stLinkCriteria

MsgBox "Did it wait?"
' Yes it did wait so the modal worked but the stLinkCriteria was ignored

Exit Sub
MsgBox Err.Description
Resume Exit_cmd_LookupPostCode_Click

End Sub

If I use DoCmd.OpenForm stDocName, , , stLinkCriteria then the form opens as intended with the data I want but obviously not modal.

Form B -Designed as a multi-record form so the user can see all the addresses that match the post code entered. In the design there is a button against each record so that the correct address can be selected. The code then combines all the address fields together, gets rid of blank lines and inserts CRLF so that the address can be returned in a single field to populate the address field on form A. The final action of the code on the button is to close Form B and return control to Form A.

Private Sub Select_Address_Click()
On Error GoTo Err_Select_Address_Click
Dim msg As String
If Address1 "" Then
msg = Address1
End If

If Address2 "" Then
msg = msg + vbCrLf + Address2
End If

If Address3 "" Then
msg = msg + vbCrLf + Address3
End If

If Address4 "" Then
msg = msg + vbCrLf + Address4
End If

If PostCode "" Then
msg = msg + vbCrLf + PostCode
End If

MsgBox (msg)
getReturnedString = msg

Exit Sub
MsgBox Err.Description
Resume Exit_Select_Address_Click

End Sub

Anyone any ideas what I'm doing wrong in the first problem where I can't get the form to open modally and with the correct data displayed. And what do I need to do to get the data in the getReturnedString variable returned back to Form A and in to a field on Form A called Address.

Thanks in advance for any help offered.


I am at my wits end with this survey!!! Currently, I am trying to get my form to save data properly. My survey is set up such that each piece of data (Questions, Answers, Completed Surveys, Answers to Completed Surveys, etc.) each has its own table, and they are linked in Relationships by their Autonum ID #s. My problem is the form I am using to enter data in the Completed Survey table and the Survey Results table. This form is mainly just a link for the 2 subforms in it.

My form has a subform at the top that enters survey demographic data into a query that updates the Completed Surveys table and the Patients table...assigning a number to that particular survey and saving the demographic data on the patient.

The bottom half of the form has a subform that moves from one question to another (questions are in a query) using a "Next" button on the primary form. The answers for each question are selected in a dropdown box (another query) and are saved in the a query based on the results table, along with the Completed Survey ID from the top half of the form...or that is what is supposed to happen.

I have identified the problem (I think) by creating visable text boxes in different places so that I can see what the computer thinks the different ID#s are in different places. The Completed Survey ID in the top subform is copied to a global variable referenced by the bottom subform. The problem seems to be if I put the command to copy to the global variable what is in the Upper CompletedSurveyID text box under "Load" in VBA, it works (the lower subform has the correct CompletedSurveyID), but I get messages that a related record is required in the Completed Survey table, and the questions and answers no longer match up consistently. When I put the commands in "Current", the error messages go away, but the 2 subforms no longer match (the bottom subform holds the number from the first record in the table.

What seems to be happening is that the lower subform needs the upper subform to refresh to take the correct number, but for some reason when this happens I get errors.

Any thoughts?! I really need to get this functioning ASAP. My boss is leaning on me & I have been working on this for several weeks! I can pretty it up later...I just want it to WORK!

BTW...I am using Access 97. Thanks for your time!

I am doing something not normally associated with Access. I'm writing an analytical blackjack program. Anyone who has played the game is probably familiar with a basic strategy chart. It has the ten possible dealer up-card values across the top, and the twenty-odd possible player hands along the left margin. Where the column and row intersect one finds the best way to play that particular combination, be it H(it), S(tand), SP(lit), or D(ouble).

I have recreated that basic strategy chart in a table, with eleven fields in it. The first is Hand, and the rest are the numbers 2-10 and A for Ace representing the dealer up card.

The Hand field has a separate record for each possible player hand, and as one progresses across where it intersects with the dealer's upcard value the appropriate move is stored.

I want to introduce a variable into a DLookup statement so that I can look up the value which corresponds to an up card of say, 2, for example, and a player total of 12. To do that with a standard DLookup statement is quite simple. DLookup("[2]","[Strategy]","[Hand]="12") (My lookup table is called Strategy.)

The problem is that I want to be able to pull the value of the dealer up card (2, in this case) from a form control or other source as a variable, so that if the next hand the dealer's up card is an 8, the statement would begin with DLookup("[8]"...

The Where clause is already ready to accept a variable, so that's not an issue, but in virtually all applications the table field to be searched is fixed, and that's where I want to introduce a variable.

I tried something that almost works. I attempted to concatenate the DLookup expression as text, using a variable where I indicated. The concatenation looks like this.

"DLookup("& """["&Forms!Play!Decks&"]"&""","""&"[Strategy]"&""")

When preceded by the Print command in the Immediate Window, it returns the following: DLookup("[7]","[Strategy]") I haven't bothered with the Where clause yet and won't unless I can get the basic expression to work.

The [7] is a variable brought into the expression from a test form, and the syntax comes out exactly right, but it doesn't actually evaluate the expression. It displays it as the concatenated text string above instead. No combination that I could find of equal signs or other means displayed the actual lookup result by evaluating the concatenation.

This was just something I tried to see if I could make it work, but it appears to only partially work, at least bringing in the value from the outside field. And while the Immediate Window results are encouraging, I can't utilize the same expression anywhere else in Access. It displays a "Can not Parse" message when I try.

All this is a long way around to get back to the basic point. I want to be able to evaluate a player hand vs. a dealer up card, and let the computer choose the appropriate response from the table, and so far I'm unable to do that. Does anyone have any ideas how to a) introduce a variable into a DLookup statement, or b) to accomplish what I'm trying to do by another means?

All help would be appreciated.

I have a database that I wrote a few years ago in Access 2000 originally. It has a form with some command buttons to open reports with data from the current record displayed in the form. Here's the code for one of the buttons:

	Private Sub AcceptanceLetter_Click()
Dim strReportName As String
   Dim strCriteria As String
    DoCmd.RunCommand acCmdSaveRecord
   strReportName = "rptAcceptanceLetters"
   strCriteria = "[FirstName]&[LastName]='" & Me![FirstName] & [LastName] & "'"
   DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

When I click that button, I get "Run time error '2427': You entered an expression that has no value."

But if I open the report without the WHERE clause in the OpenReport command, it opens just fine, though it displays all the records.

I have this same code in another database and it's working just fine.

Here's the only code in the report I'm trying to open:

	Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim strBody As String
Dim strPart1 As String
Dim strstartdate As Integer
strstartdate = [WeekendStartDayOfMonth] + 1

strBody = "Congratulation!  I am happy to confirm that arrangements have been made for you to attend the " & _
"Church Retreat " & [WeekendID] & " weekend to be held " & [WeekendStartMonth] & " " & [strstartdate] & " - " &
[WeekendEndDayOfMonth] & _
" at " & [LocationChurch] & ", " & [LocationAddress] & ", " & [LocationCity] & ", " & [LocationState] & "." & _
"  If for any reason you will be unable to attend this weekend, please let me know as soon as possible, because others may be
on a waiting list to attend.  If you have any questions or concerns, please call me.  We have a fun filled weekend planned,
and we are pleased that you will be with us."

Body.Value = strBody

End Sub

If I don't include any of the database fields in the strBody variable above, it also opens fine with the WHERE clause intact.

This used to work just fine when it was originally written. I'm not sure why it doesn't anymore. The database has moved to a different computer running Windows 7, and it's now on Access 2007 instead of the original Access 2000.

Any help is appreciated. Thanks!

I am trying to import records from tally erp 9 through xml.I have created database & form to import from xml file created by tally(tally is name of my accounting software).its also importing database but every time i have to do this import thing manually.Do any one have any macro for automatic importing the data from the specified xml file every time i open the ms access database?

I am using ms access 2003.while importing data from the xml file i keep on the option "append data to existing table(s)" to get only update entries (file>get external data>import>xml>options>append data to existing table(s))

Please help me out.....its very urgent......thank you.
Waiting for reply

while surfing i came accross one page from msdn database which might be useful

__________________________________________________ ______________
Bind an Access Form's Record Source from an XML File at Runtime
Tim Getsch
Frank Rice
Microsoft Corporation
May 2004
Applies to:
Microsoft® Office Access 2003
Summary: Learn how to bind a form dynamically to a recordset created from an XML file. This simple application can be the starting point for a powerful solution for your customers and should be considered a part of your arsenal when developing Access application. (8 printed pages)

Import XML and Create an Access Table
Function to Determine the Table Name
Routine to Bind a Recordset to an Unbound Form

With the prominence of Extensible Markup Language (XML), it is likely that at one time or another while developing solutions in Microsoft® Office Access 2003, you will need to import and use data from an XML file. Likewise, there may be instances where you need to bind the record source of a form at runtime. Fortunately, both of these operations are relatively easy to create and are equally easy to combine into one. Among the benefits of combining the operations into one smooth function are that you remove a level of complexity from your users. This also makes your solutions appear more professional as these operations are completed behind the scenes and away from your customers.
This article demonstrates programmatically importing an XML file to an Access table. A recordset is then created from the table and dynamically bound to a form. Note The examples in this article are written using the Data Access Objects (DAO) library. For similar examples using ActiveX Data Objects (ADO), see the book Programming Microsoft Office Access 2003 from Microsoft Press written by Rick Dobson.Overview

This application works by first prompting the user for the path and name of the XML file to be imported. The code then sets a variable to the current date and time. This variable is used later to help find the table created during the import operation. Next, the procedure imports the XML file and creates the table. Then, the RetrieveNewestTableName function is called with the current data and time variable created earlier. This function retrieves the name of the table created during the import. The returned value is then checked to make sure it isn't empty and thus, generate an error. Next, the user is prompted for the name of the unbound form. The recordset will be bound to this form. Finally, the subroutine calls the BindRstToUnboundForm function that binds the table's recordset to the form's Recordset property.
Next we'll look at the function that determines the name of the newly created table. To understand why this procedure is necessary, consider that the table created during import is based on the name of the XML file. If the name of the input file matches the name of an existing table in the database, the new table uses the name of the existing table concatenated with a number incremented by 1. So, for example, if the name of the XML file is Employees.xml and the Employees table already exists in the database, the new table is created as Employees1. If you import the file again and the Employees and Employees1 tables exist, the new table is designated Employees2. So to make sure that the recordset is created from the correct table, it is important to determine which table is the correct one.
To determine the correct table, the code loops through the list of tables and first checks for ImportErrors table and, if found, ignores the table. The ImportErrors table is created if there are errors during the import of data from, for example, the XML file. Because this could be listed as the latest table, it is eliminate in the code. Next, the variable containing the current date and time is compared to the DateCreated property of the each table and the name of the latest table is stored in a variable. The name of the table is then returned to the calling procedure.
Once the code determines the table name, the name of the table and form are passed to another function that creates a recordset based on the table. The procedure then assigns the recordset to the Recordset property of the form. The form is displayed and the code displays a test record in the Immediate window of the Microsoft Visual Basic® Editor to verify that the routine is working correctly.
Import XML and Create an Access Table

The code in this section demonstrates how to use the ImportXML method to import data from an XML file and create a table.
Ensure that you have an XML and XSD file in the correct format such as those created by the ExportXML method. You create XML and XSD files from Access by using the ExportXML method in a standard module in the Visual Basic Editor or on the File menu, by clicking Export and then selecting XML in the Save as type drop-down box. To use the ExportXML method, the following code example exports the table named Customers in the current database to an XML file; the data and schema are exported as separate files.

Application.ExportXML _
ObjectType:=acExportTable, _
DataSource:="Customers", _
DataTarget:="Customers.xml", _

Next, ensure that you have an unbound form with fields similar to the imported table to bind to the recordset. For the purpose of this article, you can create one from the existing Customers form in the Northwind sample database. To do this:Start Access and open the Northwind sample database. By default, this database is located at C:Program FilesMicrosoft OfficeOffice11SamplesNorthwind.mdb. Click the Customers form. On the File menu, click Save As, type a name for the new form (Customers1, for example), and then click OK. Make sure that the Record Source property is blank by opening the form in Design view. On the View menu, click Properties to open the Properties window for the form. Click the All tab, scroll to the top to the Record Source box and clear it, if necessary. Then, create another unbound form. Note This form is different that the form created in step 3 and is used to run the procedures that make up the application. Add a command button by performing the following steps:In the Database window, click Forms under Objects. Click the New button on the Database window toolbar. In the New Form dialog box, click Design View. Leave the table or query source drop-down box blank. Click OK. Double-click a command button from the Toolbox to add it to the form. If the Toolbox isn't visible, click the View menu and then click Toolbox. Click Cancel on the Command Button Wizard dialog box. Position the command button in the center of the form. Add code to the command button with the following steps:With the form in Design view, double-click the command button. In the Property window, click the Event tab, and then click the OnClick property. Click Build button next to the property box to display the Choose Builder dialog box. Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module. Next, set references to the Microsoft Office 11.0 Object Library library and the Microsoft DAO 3.6 Object Library library with the following steps:On the Tools menu, click References. In the Available References box, select the check box next to the Microsoft Office 11.0 Object Library library. Next, select the check box next to the Microsoft DAO 3.6 Object Library library reference, if necessary. Add the following code to the OnClick event procedure between Sub and End Sub statements:

Dim fd As Office.FileDialog
Dim strTableName As String
Dim strFormName As String
Dim datBeforeImport As Date
Const strFileExt = ".xml"

On Error GoTo Path_Err

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Filters.Add "XML", "*.XML"

'User didn't enter a file path.
If fd.SelectedItems.Count = 0 Then
MsgBox "You must select an " & _
"XML file. Please try again."
Exit Sub
End If

'Set variable later used to find table created
'by ImportXML method.
datBeforeImport = Now
'Invoke the ImportXML method against the xml file.
Application.ImportXML fd.SelectedItems(1), acStructureAndData

'Check to make sure that RetrieveNewestTableName
'function doesn't return an error code which would
'generate a false error in this procedure.
strTableName = RetrieveNewestTableName(datBeforeImport)
If strTableName = "" Then
Exit Sub
End If

'Prompt user for name of form to use.
strFormName = InputBox("Type the name of the form you want to " & _
"use. It should have the same fields as the " & _
"recordset (table) the form will be based on.")

BindRstToUnboundForm strTableName, strFormName

Exit Sub

If Err.Number = 31527 Then
MsgBox "The XML file was not found. Check the spelling " & _
"or that the file exists and try again."
GoTo Exit_Sub
MsgBox "Operation aborted for the following reason. " & _
vbCrLf & "Error Number: " & Err.Number & " " & _
vbCrLf & "Error Description: " & " " & Err.Description
GoTo Exit_Sub
End If

This subroutine essentially imports a XML file and creates an Access table. It also calls the other procedures in the application.
The procedure starts by displaying a file dialog box using the FileDialog method of the Application object, filtered for XML files in order to prompt the user for the name and path to the XML file. Then, the code sets the variable that compares against the creation date and time of the table created by the ImportXML method. Next, the subroutine calls the ImportXML method to read the XML file and create a table for the data. The subroutine then calls the function RetrieveNewestTableName and checks to see if that function has returned an empty String. Next, the procedure prompts the user for the name of the form that the recordset will be bound to. Then the BindRstToUnboundForm function is called.
Function to Determine the Table Name

The following function cycles through a filtered list of tables in the database and determines the name of the table just created during the import of the XML file.
Add the following function in the area outside of the previous subroutine.

Function RetrieveNewestTableName(datStartDate As Date) As String
Dim datDateComp As Date
Dim strNewestTableName As String
Dim tbl As DAO.TableDef
datDateComp = datStartDate
For Each tbl In CurrentDb.TableDefs
If tbl.DateCreated >= datDateComp Then
If Left(tbl.Name, 12) = "ImportErrors" Then
' Ignore ImportErrors tables
strNewestTableName = tbl.Name
datDateComp = tbl.DateCreated
End If
End If
Next tbl
RetrieveNewestTableName = strNewestTableName
Debug.Print RetrieveNewestTableName
End Function

This procedure determines the name of the table created during the import operation. It does this by comparing the DateCreated property of the tables.
The procedure first sets a local variable to the date and time that later will be compared to the creation date and time of each table. Then, the procedure loops through each table in the TableDefs collection of the CurrentDB object and, first, determines if the current table is the ImportErrors table. If there were errors during the import process, the records that weren't imported are stored in this table. Since this table might be the latest created, it would erroneously be bound to the form if selected. Next, the For Each...Next loop compares the creation date to (initially) the current date. If the creation date and time of the table is larger, then the name and creation date of the table are stored in variables. The cycle repeats and the comparison is now between the last table that met the criteria and the current table. Either way, when all of the lists of tables are examined, the code passes the name of the latest table to the RetrieveNewestTableName procedure and the table name is then displayed to the user.
Routine to Bind a Recordset to an Unbound Form

The following subroutine illustrates dynamically assigning a recordset to an unbound form at runtime. Insert the procedure to the area outside the previous function:

Function BindRstToUnboundForm(strDataSource As String, strFormName As String) As DAO.Recordset
On Error GoTo ErrorHandler

Set BindRstToUnboundForm = CurrentDb.OpenRecordset(strDataSource, dbOpenDynaset)

'Assign recordset to Recordset property of form.
DoCmd.OpenForm strFormName
Set Application.Forms(strFormName).Recordset = BindRstToUnboundForm

'Print record to check that things went smoothly.
Dim fld As DAO.Field
For Each fld In BindRstToUnboundForm.Fields
Debug.Print fld.Name & " = " & fld.Value

Exit Function

Select Case Err.Number
Case 2102
MsgBox "The form was not found. Check the spelling " & _
"or that the form exists and try again."
GoTo Exit_Sub
Case 2494
MsgBox "You must type a name for the form."
GoTo Exit_Sub
Case Else
MsgBox "Operation aborted for the following reason. " & vbCrLf & _
"Error Number: " & Err.Number & " " & vbCrLf & _
"Error Description: " & " " & Err.Description
GoTo Exit_Sub
End Select
End Function

This subroutine starts out creating a recordset from the table created with XML data. It does by using the OpenRecordset method of the CurrentDB object. Next, the unbound form is displayed using the OpenForm method of the DoCmd object. Then the table's recordset is assigned to the Recordset property of the form. And finally, a test record is displayed to verify that the application executed as expected.

Dynamically setting the record source of a form is a handy way to make your forms more versatile. Likewise, programmatically importing an XML file to a table is a quick way to put that data to use. Combining the two operations hides a level of complexity from your users. This simple application is the starting point for an even more powerful solution for your customers and should be considered a part of your arsenal when developing Access application.

__________________________________________________ _______________


I am a little experienced with VBA from an excel point of view, however, am a complete newbie to access. In that relation, I am running into a probably minor problem:

I wrote a piece of vba which lets me create an email with data from the entry currently shown on the form. However, since I do not want to write the whole email body into the sub, I am trying to pull the strbody variable from another table, specifically a memo field of another table. Now my problem is, HOW can I address one specific piece of data of a table?

here my line:

	Private Sub SendEmail2_Click()
Dim EmailApp, NameSpace, EmailSend As Object
Dim strTitle, strFn, strLN, strbody As String

Set EmailApp = CreateObject("Outlook.Application")
If EmailApp Is Nothing Then Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

strTitle = Me![Title]
strFn = Me![First Name]
strLN = Me![Last Name]
strbody = "Dear " & strTitle & ". " & strFn & " " & strLN & "," & Chr(13) & Chr(13)
strbody = strbody & "Warm greetings from me!

If IsNull([Email]) Or ([Email]) = "" Then
       MsgBox "There is no E-mail address entered for this person!"
       Exit Sub
    With EmailSend
        .Subject = "Warm Greetings from Crowne Plaza Shenzhen!"
        .To = Me.Email
        '.CC = ""
        '.From = ""
      .Body = strbody
        '.Attachments.Add "C:attachment.txt"
        '.Attach = strFilePath & "" & strFileName
        '.AttachFile strFilePath & "" & strFileName, strFileName
    End With
End If

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

End Sub

Now instead of using the bold part of the code, I would like to pull the email body from another table named 'Emails' and its column 'Message'. The message I need would have keynumber 3, respectively is the first line of data of the 'Message' column.

sort of like this:

	set strbody = Emai!Message!3

but without success.

Can someone point me in the right direction please?

In my table I have one field called Description and each record has unique description. I also have a field called Category with values called Level1, Level2 and level3 and this field Category are what separates the 3 from each other.

In my form called Main (temporary container that holds the values for different variables), I have 3 textboxes that stores the values from the 3 types of levels called: txtCuttingToolsDesc, txtInsertDesc and txtAdapter.

Everyone knows that Access does not allow duplicate names, meaning I can't have a description textbox 3 times.

These values are being saved in another table/form called Layout as a recordset collection then mail-merge into Word document via automation.

When creating a recordset, one complete record may or may not require all these 3 types of levels but for sure at least 2 types are required.

So, in my table/form Layout where these records are saved as a recordset collection, I also have 3 fields that referenced these 3 types of descriptions.

When making a change in one of the descriptions in my underlying table how can I synchronize the update into the recordset using a command button?

Any help with a code is very much appreciated.

I have inherited a very complex Access app. I need to run bits of the user interface from code, which I do by calling the procedures that would otherwise be invoked by the user clicking on button/popupmenu items.

1. Open a form from another form (call a sub)
2. Fill some data in the forms's fields
3. Save (call a sub)

My code "works" exactly every second time. VAriable initialization is screwed up in the form every second time, and prevents saving. I guess this is a reminescence from the previous and seemingly succesful run, that presumably leaves some unfinished business.

If I comment out the code calling the save Sub, and in stead click on the Save button, it works every time.

Now, I added one erroneous line of code after calling the Save-bit (as the last line in my test sub) so that the debugger stops the code. And now, miraculously, the code executes fine every time. I have to click the End of the Debug Dialog each time, but the code itself runs fine, apparently properly storing all that needs to be stored.

The information I have provided is very unspecific, but I am just looking for an inspired guess as to what might be causing all this. There is more than a thousand lines of code involved in 1. and 3. above, in about 5 layers.

(This thread is a continuation from here: , where I have a reference problem)

We have one system written in C# and our access database. At this moment we are able to pull open a form in access from our C# program. What we want to be able to do is open the form to filter to a specific record. Can we do this by passing a variable? What would you guys suggest?

Prevent Parent form from closing Tab Control Sub Form from closing until required fields are completed - unbound form.
(see attachment)

Until now, a function determines if all required fields are completed and returns a True/False. This sets an Application Global Variable. The Close button checks the Global variable and with an If statement allows / prevents the Close code from executing.

It would appear that if a Parent is issued a Close, none of the child objects can stop the close process.

While setting a Global variable does work, just wondering if there is any better solution? My number of Global flags are getting up there as this one application is growing.

This post is great to prevent users from changing to another tab until the required fields in the first tab is satisfied.

I'm attempting to utilize a Static Variable to retain a value while filling a listbox with a UDF, in hopes of gaining some performance increases in the creation of a cumulative sum.

I'm using code adapted from the example here:

as pointed out to me in a previous thread by ByteMyzer.

Many thanks once again for that.

I got the idea of the Static Variable from the brief reference to the concept in the MSFT Article.

I've attached a sample DB with a simple form with one 3 column Listbox. The recordsettype property of that ListBox is set to this UDF:

	Public Function List_CumulativeTest(fld As Control, id As Variant, _
    row As Variant, col As Variant, code As Variant) _
     As Variant

Static v As Integer
Dim r As Integer

Select Case code
        Case acLBInitialize         'Initialize.
            List_CumulativeTest = True
            'v = 0
        Case acLBOpen               'Open.
            List_CumulativeTest = Timer 'Unique ID.
            'v = 0
        Case acLBGetRowCount        'Get rows.
            List_CumulativeTest = 100
        Case acLBGetColumnCount     'Get columns.
            List_CumulativeTest = 3
        Case acLBGetColumnWidth     'Get column width.
            List_CumulativeTest = -1    'Use default width.
        Case acLBGetValue           'Get the data.
            If row > 0 Then
            If row = 1 Then v = row
                Select Case col
                Case Is = 0
                    List_CumulativeTest = row
                    v = v + row
                Case Is = 1 ' Create Cumulative Sum using a Static Variable??
                            ' Works correctly initially, but not after scrolling in List Box
                    List_CumulativeTest = v
                Case Is = 2 ' Create Cumulative Sum with a Loop starting at Row 1
                            'Works every time
                    For r = 1 To row
                        List_CumulativeTest = List_CumulativeTest + r
                    Next r
                End Select
            Else ' Set Header Row
                Select Case col
                    Case Is = 0
                        List_CumulativeTest = "Next Variable"
                    Case Is = 1
                        List_CumulativeTest = "Cum. Sum: Static Var"
                    Case Is = 2
                        List_CumulativeTest = "Cum. Sum: Loop"
                End Select

            End If

    End Select

End Function

This works fine for the initial load, but once you begin scrolling around, the method using the Static Variable fails to produce a consistent result.

I'm trying to make this work to optimize another form that uses this technique to fill a list box where I generate a cumulative sum with the looping method outlined above for column 3. In my real example, it's not a simple linear function as above. As a result, when scrolling towards the bottom of a many hundreds of row long list, you start to see a hit on the performacne as the loop starts at row one everytime to recalc the cumulative sum needed of the row you're displaying.

I thought if I had a way to capture the value in the previous row, and then just submit the new row's variable to the equation, i'd see big performance gains. I attempted to grab the previous row's value with a statement like:


but while processing, the lbox still contains Null values, so that didn't work. thus my attempt to use the Static variable concept.

Any insights, or pointers much appreciated.

We have just sold our first database which is written in Access 2000. However the buyer runs 2003 and we are having trouble with getting it to run correctly. Does anyone know anything about programming differences between MS 2000 Access and 2003?

Also is it possible to install both 2000 and 2003 on one computer (and also 2007)?

So far our problems are:
1. We are tripping their security alerts.
2. We can't seem to read public variables from another form.


HI, I've put this in general as the way I am doing things seems very long winded and so I was wondering if anyone had any better ideas. This is also the reason for the long explanation. For people with less time my current specific issue is the bit in bold at the end.

I was hoping someone would be able to give me some ideas on the best method to do the following.

My boss wants a great amount of felxibility on the output that can be generated by the database I'm creating primarily that any field can be compared about any other and a simple report with min max count etc with some nice graphs can be shot out.

I'm restricting this to fields in the main table "tblMainData", this table contains a lot of project data, some fields are text, some dates, some numbers and the vast majority contain IDs that are used to look up the field value from another table "tblCodeValues".

My thinking is that I have a form that the user uses to select which fields they want to analyse. This contains two list boxs. The first "lsMainCriteria" is a single select list box in which the user choses the main field they're interested in. The second "lsSubCriteria" is a multi select list box from which the user selects the fields they want to compare to the main criteria. Selections in this listbox will be limited to only those of similar datatypes (text + lookup fields, numeric, date) and then depending on the data type one of three corresponding simple generic reports will be populated with the data.

The problem I am having currently is with the lookup values as obviously the default value returned without doing a lookup is the codeID rather than the text. My first thought was to initially create a query that has all the looked up values in it and then base the rest of the process off this. However the first part of the flexibility comes from a filter by form form which works nicely off the original data but is very slow when based on a query of looked up data, so I thought I would leave the looking up of the actual values till as late in the process as possible when there will be the minimum amount of records and fields.

This is leading me to needing to include a Dlookup in an SQL statement I am constructing in VBA and I can't figure out quite where I need the quote marks to make it work. How do I write a dlookup in SQL withing VBA? I have tried:

	 strLookUpSubRptQry = "DLookup([CodeNameShort], tblCodeValues, [CodeID]=[" & Me.lsSubCriteria.Column(1, varCategory) & "])
AS Field" & k


	strLookUpSubRptQry = "DLookup('[CodeNameShort]', 'tblCodeValues', '[CodeID]=' [" & Me.lsSubCriteria.Column(1, varCategory) &
"]) AS Field" & k

and a variety of other methods and still can't get it to work.

Both followed by

	StrFullSQL = "SELECT [ProjectID], " & strLookUpSubRtpQry & " FROM qryFiltered

(this is a bit shorter than the actual code)

so if anyone can help with the specific issues or have any suggestions on other means to perform this task it would be appreciated. I know this isn't quite how Access is designed to work and I do have a backup to export the data to Excel to let them play about with it here, but I really would like to be able to incorporate this feature, and I'm sure it can be done however long winded


I'm creating a mdb for a DVD hire company.

I'm trying to create a form which loads on startup (done this part)... which will let the user select which branch they are using (one of three) and then proceed to another form. When the user selects the branch '' I want the system to store the branch name and use it in the label which follow.

Attached are two pictures to illustrate.

Hope it makes sense what I'm trying to do.

I guess it's got something to do with passing the parameter from the welcome screen and then using that variable on other forms?!?!?

Not finding an answer? Try a Google search.