how do i pass data from one form to another Results


Page 1 of 2.
Results 1...20 of 34

Sponsored Links:



I am creating a data base for multi user and permission controls. For example, one user has view only permission where as another has read permissions. In the log in form, the user permission is verified before logging into the navigation form. I created different navigation forms for different permission controls.

My problem is how do i pass the variable value that contains the "user id" from the login form to all other forms. By doing this I cud load the corresponding forms for different employees with different permissions.

I am newbie in VBA coding. Coudnot make out a lot from search I made.




Hi all,

I've got a form (frmPatientDetailsWithTabs) which is used to display a patient's record. This is based on a query called qrypatientrecord, and on this form is a command button cmdViewReport.

When this button is pressed, a form opens (frmViewReport) and on this form is a dropdown box which uses a query to display all reports for that patient - well this is the intended use. When selected the user then clicks on a button to preview the report.

I use the form in another location where _all_ reports are viewed in the dropdown box (this works fine), with the following columns:

Patient ID Surname First Name Report ID

and the query for this is as follows:


	Code:
	SELECT qryPtdetailsRCAforlistbox.PatientDetailsID, qryPtdetailsRCAforlistbox.PatientDetailsSurname,
qryPtdetailsRCAforlistbox.PatientDetailsFirstName, qryPtdetailsRCAforlistbox.RCAID 

FROM qryPtdetailsRCAforlistbox 

WHERE (((qryPtdetailsRCAforlistbox.PatientDetailsID)=[txtPatientDetailsID])) 

ORDER BY qryPtdetailsRCAforlistbox.PatientDetailsSurname;

I have copied the form and renamed it so that I can make modifications to it without affecting the original form (which works fine).

If I want to use this form to display in the dropdown box only the reports for the patient currently active on the original form (frmPatientDetailsWithTabs), how would I do this?

The VBA associated with cmdViewReport is as follows (not sure if it's relevant or not)


	Code:
	Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmViewReport"
       
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewRCA_Click:
    Exit Sub

Err_cmdViewRCA_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewRCA_Click
    
End Sub

I have tried the following:
Using LinkCriteria to set PatientDetailsID in the qryPtdetailsRCAforlistbox to the value displayed on frmPatientDetailsWithTabs Using the criteria on the query qryPtdetailsRCAforlistbox to try to pick up the data from the form Google search & forums (but not sure what to search for really!) I think perhaps I have the syntax wrong or I am totally barking up the wrong tree :|

Ruth




I've been struggling with this problem all week, so if there's anyone who can respond quickly I'd be much obliged as I’m about ready to shed a tear or two over this!

I have set up the following. (Note I've included ONLY the field names relevant to my problem.)

A table called CAR with a field called RequestorID*.

A table called Employees with the fields EmployeeID*,
LastName, FirstName, Phone, Email, Mobile

* The RequestorID and EmployeeID are linked.

A query called EmployeesQuery that gets data from the Employees table:

EmployeeID (primary key)
mName: [LastName] & ", " & [FirstName]
Phone
Email
Mobile

A form called frmCreateCAR with the following fields, in addition to other bound
fields from the CAR table.

Name (unbound)
Phone (unbound)
Email (unbound)
Mobile (unbound)

Okay, so here's my problem. When users open the frmCreateCAR form, I have a button they can click to open a form called called frmEmployees, which has all the information from the EmployeesQuery

When the user finds the name they want, they double-click the Name field. The EmployeeID is stored and frmEmployees closes.

The user is returned to frmCreateCAR where the Name, Phone, Email & Mobile values for selected EmployeeID are populated in their respective fields

It sounds like this should be SO easy, but I can't for the life of me figure out how to do it. (Another user a week ago suggested a combo box could do this easily, but it isn’t what I want nor need in the grander scheme of things.) Any help speedily delivered will be so very, very, very appreciated!

Christine




Hi,

ET is Calling Home again on the same subject... either I am dumb, but I just cannot seem to grasp how to pass the info on the one form to the other form. This is what I am trying to do.

1. Table 1... tblMaster with the following field - Record_Id (Autonumber - Primary Key), Building_Name (Text - using Lookup Combobox to populate), Room_Nbr (Number - using Lookup Combobox to populate), Capture_Date (Date) and Capture_Id (Text - using Lookup Combobox to populate)

2. Table 2... tblSlave with the same fields, except, the Record Id is not an autonumber and primary key only a Number and all the other fields do not use Lookup ComboBox to populate and additional fields like bathroom, room, closet, balcony. The intention here is to take the information from tblMaster and populate the data on tblSlave.

3. I created a Form called frmMaster and enter the necessary information, but I now want to populate the common info on the two tables. I created the relationship between the 2 tables.

4. I created a second Form frmSlave.

5. On frmMaster I created a cmd button using the wizard to open frmSlave. When I use this as is, it opens the frmSlave, but no data is pulled thru. When I change the event on click from embedded to event procedure and write the following code, it give me an error: Compile Error (Warning) Expected: =.

Private Sub Command66_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmSlave"
stLinkCriteria = "Hotel_Name =" & Me.Hotel_Name
DoCmd.OpenForm(stDocName,acNormal, , ,acFormAdd, ,StLinkCriteria)
End Sub

Even though it is a warning, it does not even open the frmSlave when I use the code.

Please bear in mind that I am a novice when it comes to Access development, but a very knowledgable developer in Natural Adabas/Construct and Cobol and Cobol/CICS... yes! I am a legacy systems analyst and fully understand how the logic works with RDBMS. I can create tables and its relationship... queries, forms, reports.

IF what I am trying to do is not the way to go then can some please help me... you never know, you may need assistance in the programming language that I have over 20yrs experience in... so common guys/gals... help this old man...




I'll try again.

I have two forms:
What I'd like to do is have a value from a Combo Box pass to another Form and populate a field within that form, so more fields can be updated with additional data to complete a new record.

1. I tried setting the value of second Form field to the first Form Field
2. Me.list45.value = the second form and field
3. tried setting control setting = to query that also uses the value

from the first form field (the query works fine) =[Ending alance1]! [Document_Number]
4. oh tried the Access help button, which BTW I find generally unHelpful.
5. Searched the this forum.
6. Googled this question thinking I might get lucky (I didn't)
7. I think I'd better pick up and Access 2007 book

Has anyone done this? If so how?




Hi all

long time lurker now needing some help! Firstly I have to say thanks for all the help I've received but not asked for but now I find I need to ask a question and hope someone can help.

I have created a database that uses a menu system and pop up forms and all is working nicely, now I am trying to enhance the experience by enabling new records to be created that requires other forms to open that previously required you to return to the menu.

This is working nicely using global variables and conditions so forms know if they were opened from the main menu or from a form. All works appart from one scenario.

I have a form that has a linked subform:

form = risk
subform = exception

for the subform to have data it has to link to the risk, I do this by the risk ID field. This works

Waht I now have is a button on the risk form to enable a user to create a new exception and automatically link the two together. This works nicely but requires the risk form to close and reopen for the data to all link.

What I do is this:

user clicks new exception
a global variable records the current risk ID and passes this to the new open exception form
the exception is created and passes the exception ID back to a combo box on the risk form
this combo box records a value into the table exceptions

What my problem is is this, if I use the combo box directly it updates the record in the table and the subform maps to the new link

when I pass the value to the combo box from the new exception form it updates the combo box when it closes, but the combo box does not record the value until the form is closed or record is changed

so the simple question is this, how do I get the combo box to update/save its new value from the close event of the exceptions form?

I have the combo box requery to update its list so it can see the new exception so that works but how do I get the form to save the updated value when passed from a vba macro?




Hi,

Would really appreciate help with this problem, I do not know what syntax to use in order to pass my primary key from one form to another via a command button. Eg: I need client number to be passed on from frmClient Details to frmMain Network so I do not have to re type it to link the two lots of data, I know I need to reference it but how??

This is my code so far, what next:

Private Sub DetailstoNetwork_Click()
On Error GoTo Err_DetailstoNetwork_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMain Network"
strLinkCriteria = "[Client Number]=" & "'" & Me![Client Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormPropertySettings, , Me.OpenArgs
DoCmd.Close acForm, "frmMain Form", acSaveYes

Exit_DetailstoNetwork_Click:
Exit Sub

Err_DetailstoNetwork_Click:
MsgBox Err.Description
Resume Exit_DetailstoNetwork_Click

End Sub




Hi - I am attempting to provide a list of records via sub-form and query - with the ability to click on one record, and open another form filtered to the record selected.

I am using the OpenForm in a macro associated with an action, and the OpenForm Parameters are as follows:

Form Name: frm_FTSO
Filter Name:
Where Condition= : = "[TaskSR=" & Nz([TaskSR],0)
Data Mode: Edit
Window Mode: Dialog

The same settings on another form/query pair work fine. the problem with this one is that when the action is taken, I get an Enter Parameter Value message box requesting that I enter the search criteria for the value contained in the [TaskSR] field referenced above.

If I enter the proper data in the message box, the new form is open to the record being requested.

How do I fix or what do I look for to pass the value of [TaskSR] for the record I selected, to the where condition so that the parameter value box does not appear?

Thanks...




This is a great website, folks! Here is my situation:
Records are being downloaded from a mainframe into an Access table. Unfortunately, the field names are AugCurrSales, AugPriorSales, etc. for current year and prior year sales. I started creating reports, but already have SOOO many queries, reports, and subreports.
To get the data into a more “normal” structure, I did the following: I created 12 queries to extract the data for each month. For example, qryAugust has (among others) a Month field, Mo# field, CurrSales field, and PriorSales field. I ‘manually’ enter “August” in the month field and “8” in the Mo# field for each record.
Then I created a Union Query that merges the 12 month queries together. So now my Union Query has the same data in it as the original table, but instead of one record and multiple fields for months of Sales and PriorSales, in this Union Query each Location now has 12 records - one for each month. (PS: I can’t put an actual date in the Date field in my 12 month queries because as each new year occurs the same fields are used … otherwise I could use ‘Between’ parameter prompts).

Here’s the problem. Management wants to be able to choose a month from a form and click ‘print’ and 10 different reports will print for that month. However, most reports show (among other values and in different groupings) CurrSales and PriorSales for the month, AND Year-to-Date values! I have successfully created a Dialog Form that lets the user choose from a list of months and then send that selection to the query that the report is based upon and one report prints the month’s values. But I need help figuring out how to calculate the YTD values, how to get them on the same report as the monthly values, and how to print all 10 reports at the same time.
The fiscal year is July 1 - June 30. I don’t know VBA but want to learn it. I’ve spent 5 days reading and looking on the Internet for answers - and have learned a lot - but I need help.
Q1: To calculate YTD values, would I create Select Case code that - depending on which month the user selected in the drop-down listbox - & have the code sum the values in the CurrSales and PriorSales fields “? I.e. If Mo#=9, then YTDCurrSales = (CurrSales where Mo#=7 + CurrSales where Mo#=8 + CurrSales where Mo#=”9”). That looked like a possibility from my research. What do you recommend?
Q2: If Q1 is accomplished with VBA code, I think from my reading I can figure out how/where to type it in, but where do I point to the code so that it will run ‘after’ the selection is made on the form and pass the month and calculated values to the 10 reports? (Another Switchboard form gives the manager the option of previewing any report for any month, so I don’t want to hard-code an event in the reports if I can help it)
Q3: To automatically print the 10 reports with one “OK” command button, it appears I just need multiple DoCmd Open Report lines. If I didn’t have the YTD challenge in Q1, I’d guess that this commands could go in a macro attached to the “OnClose” event of the form or something. I’ve not tried to print multiple reports with one command button before. Please advise in this instance.
Q4: Lastly, how do I put monthly data and YTD data in the same report? I’ve not done a Cross-Tab report before but saw some references to that. But since I’m not sure how to calculate the YTD values, I can’t make headway here. Please advise.

I hope my description above is clear. I had hoped to figure this out all by myself, but am running out of time and just lost my mind. Please help. Thanks 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?

cheers




I don't know why this is so difficult - but I am sure someone here will find my error(s)!

I would like to pass the selected value (in this case a 4 digit year) from one unbound combobox on Form1 to another unbound combobox on Form2. The comboboxes are identical on both forms. Usually when I want to pass data between forms I use the DoCmd.OpenForm from a command button OnClick event in Form1:

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[cbo2] =" & Me![cbo1]
stDocName = "Form2"
DoCmd.OpenForm stDocName, , , stLinkCriteria

This does not work and I have tried every possible iteration of the stLinkCriteria to no avail. The only way I can get Form2 to open with the correct year in cbo2 is to use the OnLoad event in Form2 with this code:

Me!cbo2 = Forms!Form1!cbo1

This works perfectly as long as Form1 is still open. I would rather not use the OnLoad event because Form2 can be also opened by other forms when Form1 is closed. If I try to open Form2 from another form it gives an error message because the OnLoad event can't find Me!cbo2 = Forms!Form1!cbo1.

Obviously there is a gap in my understanding of how the stLinkCriteria functions. Can you use the stLinkCriteria to pass data between unbound combo boxes?

I suppose I could check to see if Form1 is open in the OnLoad event and if not ignore the Me!cbo2 = Forms!Form1!cbo1 statement, but I am not sure how to best do that.

Please advise, and thank you!




I wan to retrieve data in text fileds(in form2) based on passing value from a command button in form1 to form2.
I have a form1 that contains several text boxes, one of the text boxes is a "project Number", for example, the project number for the first record is "P14X" and the project number for the second record is "P14XZ" and so on..
Lets say i am viewing "record 2" and the project number is "P14XZ"...what i want is when i click "open" button to open another form, pass this project number "P14XZ" that i am currently viewing to a second form, and in the second form i should get this project number plus data in text fields that associated with that project number.
How can i do that?
Thanks in advance.




Please help me with this one!!!

The problem I am having is this. When I designed the database for our company is was only me that would be using it as no-one else in our office had any knowledge what so ever of Access or even much knowledge of computers in general. So I never designed it for anyone else to use except myself. Now however I have had to move onto other tasks and pass the data entry part of my job to my collegue the controller. (I work for a courier company). The problem is he will be entering jobs in as we receive them and most of the time we don't receive all of the relevant data until the next day. So what we are wanting to do is for him to enter all the data he has for each job and then the next day for me to complete it with the rest of the data. So that means us both be entering data onto the JobDetails form. I didn't think this could be done. So the only other way I can come up with doing this is to design another form for him to complete and somehow retreive the data entered onto that form by him onto my form that day. This doesn't sound the best way to do this to me as we will then have to copies of some of the data but I really cannot figure else what to do regarding this situation and am in desperate need of help and guidance regarding this matter.

There must be an easier way to acheive what we want to do. I am also needing to find out how I can close of most of the database to him so he doesn't accidently change anything on it. I know this can be done but am not sure how to go about it. My design up until now has worked fine for what we needed it for but as our business is expanding and I have more work to do I have to try and change the database to suit the needs of my collegue as I mentioned earlier in totally new to the computer world.

I would really appreciate any advice and guidance you may have on the above subjects as I am in desperate need to begin these changes as soon as possible.

Thank you in advance for any advice I receive from you. If it wasn't for you sharing your expertise with me I would never ever have got this far with the database and trust me it is running perfectly for me. However these new things that are coming up are threatening the perfect running of my database.
I really hope I have explained clearly what it is I want to acheive.
Thank you again. and hope to hear from you as soon as possible.




I'm sure there is an easy solution to this one, but I have a terrible time passing data from one table/query/form to another, so I'm hoping someone can help me out.

Currently, I have a form (called GroupReservations) with a combobox that list the first name, last name, and agency that someone is from. The first column is bound to AgentID and the combobox pulls from a query called [Contacts & Agents] (Agency name comes from the agents table and first and last name come from the contacts table). That info then fills in a bunch of other fields in my form -- which is all working fine.

Now, I want to allow users to add new agents or contacts if the agency or person they are looking for is not on the list, so I created a command button called NewAgent that opens up a form called "Maintain Agent List".

When users click the button, the other form opens and allows them to enter a new contact and/or agency. So far so good.

Here's the problem... How do I let my combobox know that it needs to requery (I guess) the "Contacts & Agents" query so that the new record shows on the list? If I close my form and reopen it, the new info is on the list in the combobox. But I can't get it to update automatically upon entering the record in the "Maintain Agent List" form.

Any assistance would be appreciated!

Kim




Hi,

I have multiple access databases with which I control different sharepoint sites. I also create tables (I would say reports but they are really access tables) with the sharepoint data and then those tables are fed to excel files that I then post to the sharepoint.

I have to update these excel files on a daily basis so Im trying to automate at least part of this process. I am having trouble figuring out how to:

1. Run a subroutine in another database?

2. Run a cmdButton_Click event on a form in that other database? So it would be like if I went into that other database and clicked the button I alraedy created on the form.

3. Open and close the excel file so the data is refreshed? The excel file is set to refresh data sources when opened. If this is possible, would having the database open interfere with the data refresh? How do I get around that? I know the open database interferes when I manually open the excel file. THe only thing I can think of is that running this refresh from my "master database" would work since the the databases the refresh is happening from would not be open.

4. Is it possible to pass a variable from my "master database", to one of the ones I wish to control?

Thanks alot for any help with ANY of these steps!




I've created the following tables in ms access db. The purpose is to be able to complete quality check on loan files. The loan file details are an extract from another database. The loan details will not be deleted or changed in ms access so I plan to use a linked excel workbook as a table. I will need to be able to tie back a particular questionnaire to a loan file in the loan file details linked table.

I've populated the SurveyTbl, QuestionTbl, ResponseTbl, ResponsiblePartyTbl, RiskTbl with the acceptable values and have established the relations between the tables.

*Each loan should have only one response, one responsible party and one risk level per question.

My major questions involve adding a new record to a particular loan. I envision having a form which lists out all 80 questions, the user enters the ApplicationID once, then selects the Response, Responsible Party and Risk for each question. This is where I get stumped. How do I create a form that based on the Response, Responsible Party and Risk will update the key fields listed below without the user having to know what the key values should be? I've seen people create a form that updates values in a single table but structurally I was looking to create something closer to a true database. *Note I own 2 access books and they don't seem to address my question above as the books seem more geared towards data manipulation of a finished database (example create a query off existing data in database. create a form that shows data from existing data in the database. Alter the appearance of an existing form).

SurveyTbl
ID (PK)
SurveyDescrip (type of survey example: auto loan)
(note: a survey can have more than 1 question associated to it but a particular question can only be associated to 1 survey)

QuestionTbl
ID (PK)
QuestionDescrip (example: Signed loan application in file)
SurveyID (FK)

QuesionDetailsTbl
ApplicationID (PK of loan details linked table)
QuestionID (PK of QuestionTbl specific question from QuestionTbl)
Question Description

ResponseTbl
ID (PK)
ResponseDescrip (Pass, fail,n/a)

ResponseDetailsTbl
ApplicationID (PK of loan details linked table)
ResponseID (PK of ResponseTbl)
QuestionID (PK of QuestionTbl ties back response to a specific question)
ResponseDescrip

RiskTbl
ID (PK)
RiskDescrip(low, moderate, significant, Serious)

RiskDetailsTbl
AppID (PK of loan details linked table)
RiskID (PK of RiskTbl)
QuestionID (ties back risk to specific question)
RiskDescrip


ResponsiblePartyTbl
ID (PK)
Position (processor, underwriter, funder)

ResponsiblePartyDetailsTbl
AppID (PK of loan details linked table)
ResponsiblePartyID (PK of ResponsiblePartyTbl)
QuestionID (ties back who was reponsible for issue with specific question)
Position




I have many reports which are all highly similar. Each one displays numerous individual values. Each one of these values is processed through a function that tests to see if the value is greater than a preset limit. If so the function returns the initial value, if not then a special symbol is displayed. But now my users want the aforementioned preset limit to be dynamic, i.e. they want to type it in when they open a report. How do I query the user for a single value when the report is opened and then use that value as a variable on my report.

To describe things in more detail. Say I have twenty text boxes on my form, each one has as its source a field from the underlying query. But these text boxes are invisible and the user doesn't actually see them. Next to each of the invisible boxes is another text box whose data source is a public function defined in a code module. One of the three inputs to the function is the name of the invisible text box right next to it. (The reason I did things this way was because it made it easier to change the data sources used from the underlying query.) Another one of the inputs to the public function (I call it Format_Data) is the value limit I mentioned earlier. Currently I just have a literal number entered here for every instance of the function. (I wasn't sure if every instance would use the same value limit or not). Anyway, I now need to replace those literal numbers with an actual variable, of course. And I need to assign a value to that variable somehow when the report is opened. I tried adding another invisible text box bound to a data source that didn't exist so that the report would ask the user to input a value for it at startup, but that didn't work. The textbox seemed to act as if it wasn't storing the given value until after the other textboxes had tried to load/compute their data.

If this doesn't make sense I will gladly reexplain it again, and try to organize my thoughts better. But basically I just need some sort of entry box, asking for a single value, to open when a user tries to open a report. Then the entry box should pass the user entered value on to the report somehow.

p.s. I am trying to avoid making a form for each separate report!




So I have a project I've been working on that is supposed to provide people with a distance and bearing from a known point to a list of possible emergency locations.

Essentially, I only have two tables that contain data - the known points, and the emergencies. I use a form to get the known point and a radius to search; this data gets passed to a query. I then do some heavy trig in VBA to calculate distance in nautical miles and bearing. Most of the distances I'm dealing with are are under 100NM.

Bottom line: my query works great. It filters and sorts exactly how I want and the data is displayed correctly (Bearings from 0 to 359, etc). The report however, displays crazy numbers, that seem unrelated to anything. For example, my query displays one thing (top part of the picture)
and the report another (bottom part).

And yes, the report is bound to the query, and the text boxes are bound to the appropriate fields.

I've deleted the Report and started from scratch, and still nothing.

Additional info: range and bearing are both General Numbers rounded to 2 and 0 places respectively. I used the wizard to generate the report off of the query, so I don't understand what Access is doing to the data.

Lengthy post, I realize - but I've about had it with this. 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)
Contents

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

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", _
SchemaTarget:="CustomersSchema.xsd"


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"
fd.Show

'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:
Exit Sub

Path_Err:
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
Else
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
Else
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
Next

Exit_Sub:
Exit Function

ErrorHandler:
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.
Conclusion

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.

__________________________________________________ _______________




To those reading this, I am not writing all of this to make fun of you or anyone else, but rather to boost the efficiency, working environment, community involvement, morale, and integrity of this forum.


This post is primarily for beginners, new forum posters, novice coders, and the like. Most people that are not programmers don't really know how to talk to them (us), and as a result, fire burns and fevers rise. In my opinion, if you want to make good use of your time when on this forum, learn how to talk to programmers efficiently and effectively.

If you're asking a question: PLEASE, explain it. I am not begging you to, nor do I care if you do (because I won't answer the question if I see it). Some programmers understand this delimma, but quite frankly some are very poor at recognizing it. The delimma is the poster's knowledge of the subject. In general: say in your post if you know what you're doing or not. That's way more helpful than saying something like: I have programmed Access before but have never worked with crosstab queries. That's great but it is not useful at all because the bottom line is that you don't know what you're doing.

In my experience working with, talking to, and even managing a couple of programmers, they typically are very intelligent, but many of them could use some verbal communication skill improvements. I'm not knocking programmers or the profession (or the art), as I'm sure being molded into a programmer over time has different effects on everyone. Below are some things you might want say in your post for some common situations I've seen around here. I am simply guessing that the "typical" programmer will appreciate any or all of these things you do when you communicate with them:


IF YOU WANT IT TO DO SOMETHING, BUT DON'T KNOW HOW TO PROGRAM IT...
*Tell us flat out...."I don't know how to do this"

*Respect the fact that time given to you is free and always ask a question that shows you want to learn how to do it. Something like: "Where do I start?", or "What would be my first step?"

*ASK FOR ADVICE on how to approach your project. BUT...don't detail us to death. In other words, ask it the same way you would ask Neil Armstrong this question: How did you feel when you got to walk on the moon? If he's like most, he'll answer your question than tell all the details of that extraordinary day. Hence, you'll get more information than you bargained for. Programmers love to pass knowledge on, especially stuff that WORKS and has been successful. At least I do!


IF SOMETHING IS NOT WORKING IN YOUR DATABASE...
*Keep in mind folks, that Access is one of the most complicated programs inside MS Office. It uses thousands of files to function. That being said, programmers REALLY appreciate it if you KNOW what is wrong before you ask. That way you don't write anything that cries out to us as "I'm just guessing".

*If you don't know what is wrong, tell us exactly what doesn't work. NOTHING ELSE. Just the piece that is not functioning. If a "knowledgeable" programmer replies to your post, he/she will most likely know where all of the possible bugs could be, and will tell you where to look for them. Example Question: "My subform doesn't show any records when it's recordsource table has 5 in it. That is a start. If you CAN, after you say that, give us any information that you KNOW is related to the functioning of the subform. If you simply have no clue why it doesn't work, you will only dig yourself a deeper hole by saying anything else. At this point, you will get the most out of your conversation with someone who answers if you just say, "I don't know where to go from here". The worst possible thing you can do if you're in this boat is say something like "I tried to do...to fix it", or "I know if I do....it will work". There's nothing wrong with saying stuff like that, but believe me, it sounds selfish, bullish, and over the top. People are not receptive to that.

*In my opinion, your post should ALWAYS include what you want to have happen in the end. Example: I want the textbox to show the current user of the database. Another Example: I want the query to return the 1st 10 records only.


IF YOU WANT HELP WRITING CODE...

*Personally, I completely turn off my hearing aid if someone talks to me directly about the code that they wrote and what they got it to do. Guess what!? I care, but you are overloading me with information that I can't possibly retain. So please keep it to yourself and enjoy the fact that you made something work that can't be easily explained. This goes for posts too. Most people have a specialty when it comes to code writing, be it client side, server side, or just plain proprietary languages like VBA. If you want to ask specific questions about code, explicit syntax, or language structuring, you will probably get the best results by writing it as professionally as you can. What you're looking for is someone to answer who knows exactly what you're talking about. For example, you say It tells me "Block If without End If", and you post this: PHP Code:
if myvalue = "foo" then

   for each c in me.controls
      if typeof c is label then
         debug.print c.caption
      end if
end if 
someone would popup right away and say you forgot to close your FOR() loop.

*If you simply want us to give you source code for free, make it reasonable. If you don't know what is reasonable, LEARN IT. Learn the difference between ADVICE and PROJECTS. Asking for too much free knowledge is the worst way to try and make friends in this world. Most professional programmers are way beyond what you're asking for, so they don't mind giving you SOME code for free, because most of it that they are willing to give out has no market value anymore for them (e.g. - they can't make money writing it for someone else. either that, or it's not worth their time). Example - here's some code I am willing to give for free:

	Code:
	With Application.FileDialog(msoFileDialogFilePicker)

dim varitem

         With .Filters
           .Clear
           .Add "All Files", "*.*"
         End With

             .AllowMultiSelect = false
             .InitialFileName = "c:"
             .InitialView = msoFileDialogViewDetails

                    If .Show Then

                      For Each varitem In .SelectedItems
                           msgbox "You selected a file with a " & _
                                      right(varitem, 4) & " file extension."
                      next varitem
                      

                    End If
                    
End With

an extension of this code example I would not give for free would be one that allows multiple selections of files, consolidates them into one datasheet and imports it into Access for analysis, all dynamically through VBA procedures and object library references. In my eyes, that is a small project.

*If you want helping finding a BUG, be respectful or our time by exhausting all the resources Access gives you before you post your code and say "HELP!" Remember too that programming is a god-awful thankless job by nature, so if you can make a programmer feel good about him/herself, you'll get more help (and words) than you bargained for (which is what you generally want).

*I think I speak for many programmers when I say that if you ask a question that says something like this: "I want my program to do A, B, and C. I would appreciate it if someone could provide the code to do so". It's a light tempered question for sure, but regardless, you won't get an answer, at least from me. And here's why:
1) You have shown absolutely no interest in what you're doing.
2) I suspect you're trying to get source code for free and then sell it. If you want my opinion, that's immoral. Moreover, you're not fooling anyone that cares.
3) You're not trying
4) You're not participating in sharing the information we all seek so we can grow and be better.


WANT TO GAIN RESPECT AND RECOGNITION? TRY THIS...

If possible, know what you're talking about. It makes you look good. Also, try not to mistakenly describe objects when you talk about your issue: My form's control source is "SELECT...etc, etc..." (forms don't have this property).

*I personally believe this forum is great because of the people that hang out here. There are plenty of knowledgeable people here, and lucky for you they are experts in SOMETHING specifically, but also know a little about a lot of other things. As I said before, what you want here is someone who tells you "DO A, B, C to fix your problem". That rarely happens, and most of the time it is because information is lacking to fully analyze the problem, formulate a solution, and then explain it to someone who doesn't initially understand it. Programmers REALLY like to have all the information needed to come up with as many solutions as possible that will work. In reality, it is YOUR responsibility to give that to us if you want the right answer to your problem. I am one for example, that does understand that some people who post here simply have no clue what they're doing, nor do they know what to do with a solution if one is given to them. If you're posting for a good reason and you truly need an answer from us, and you have ZERO experience related to the subject matter at hand, please say so right away. If someone does answer your question, it will save them loads of time by not having to wonder if you "know that already". For me, I have no problem answering these kind of questions. The point at which I become frustrated, and probably unsubscribe from your thread, is when I find out that you "knew that already" or "tried that already" when I spent time explaining it or typing it (whatever it is). If there is anything I dislike (and I like a lot of things in this world), it is wasteful use of time. So...in short, if you know what information we need to solve the problem, please give it to us. Don't guess. If you don't know what info we need to solve it, just say so and don't worry. We probably know what we need even before you say it. Here are some good examples of questions and the information we would need to give you the right answer the FIRST time so you can live happily ever after without much fuss:

Question: When I try to lock a textbox with VBA, it doesn't lock.
We Need: 1) the code you're using, 2) the value of the "allow edits" property of the form

Question: Why doesn't my subform show any records?
We Need: 1) subform's source table or SQL that runs it, 2) any initialization code behind it (code that is run when its loading or opening), 3) any links to a parent form it has

Question: I am getting a "type mismatch" error in my procedure...
We Need: 1) the line of code that Access says is bad, 2) what words in the code the debugger is highlighting, 3) any variable names and their data types that are inside the error-ridden code

Question: It tells me "No Current Record"
We Need: 1) what you're trying to do at that particular moment, 2) highlighted code if the debugger can be run to find it, 3) what you did right before you got the error, and any code that was run


MY THOUGHTS ON BEING A GREAT PROGRAMMER

I am nowhere near "great", but I have picked up on things that have taught me a great deal about the art of programming. Weather you're serious or not, if you are interested at all in learning anything about how programming works, be it software structuring, OS structure, script writing, or software engineering, try to post questions that will someday give others a chance to gain at least 1 more byte of knowledge. People who answer your questions will have more respect for you too!


Page 1 of 2.
Results 1...20 of 34