Passing Data From Child Form, To The Original Form
Dec 8, 2007
Hi All,
I'm not sure if this should go in here or in VB Programming, because it's to do with an Access Database but it's VBA code.
But I understand that VBA and VB are different in the way they work.
I think this may have been looked at before, but I'm not very good at using the search function. I've looked on google, but all I've seen doesn't seem to be for my use, so I came here as recommended by "RadioActive Frog" who I believe is a member here.
Right, basically, I am writing an Access database for my company's ordering system.
I have a form called "Enquiry" which has many tabs, one of these is the "client" tab in in here are many fields:
Firstname
Surname
Company
Addressline1
Addressline2
Town
County
Postcode
Phones
Fax
AltMobile
Email
ContactType
Now, there is an option to enter a new client or search the database for an existing client and it is basically the "new client" which I'm having issue with at the moment.
Basically, clicking the button "New Client" opens another form called "Client". Here, the user enters the data into fields which are the same as above (but on this contact form).
When done, they click a button called "Save and Close" which then asks the user if they want to paste their entered data into the original main "Enquiry" form.
It's this last bit, the transferring of this data I can't get my head around and would be incredibly greatful for help with.
My Code is below:
Code:Private Sub Save_Click()On Error GoTo Err_Save_Click ' On clicking save, a dialogue box will open asking if you want to paste this data ' into the enquiry form. Clicking yes will do this. Clicking no will just close the box 'declare intpress as an integer Dim SavePress As Integer 'Save Command DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'when save and paste is clicked, ask if the information should be pasted into the form or not SavePress = MsgBox("Would you like to paste this Contact Information into the Enquiry Form?", vbQuestion + vbYesNo, "Paste details") If SavePress = 6 Then 'If the enquiry form was the form which initiated the cotact form, then copy and paste 'the informtion into the enquiry form and the close the form 'If "Enquiry" = Me.OpenArgs Then <------ I have decided not to use this, as it doesn't seem to work. (I'm probably not using it correctly) Enquiry!FIRSTNAME = Me.FIRSTNAME Enquiry!SURNAME = Me.SURNAME Enquiry!COMPANY = Me.COMPANY Enquiry!CATEGORY = Me.CATEGORY Enquiry!ADDRESSLINE1 = Me.ADDRESSLINE1 Enquiry!ADDRESSLINE2 = Me.ADDRESSLINE2 Enquiry!TOWN = Me.TOWN Enquiry!COUNTY = Me.COUNTY Enquiry!POSTCODE = Me.POSTCODE Enquiry!PHONES = Me.PHONES Enquiry!ALTMOBILE = Me.ALTMOBILE Enquiry!EMAIL = Me.EMAIL DoCmd.Close acForm, "Contact" Else DoCmd.Close acForm, "Contact" End If Exit_Save_Click: Exit Sub
But It's not working. It's giving an error saying "Compile Error: Variable not defined" and it highlights the word which I've mad RED in the code above. Now, I tried changing the exclamation for a fullstop, and I also tried writing "Form.Enquiry.FIRSTNAME" (and also with exclamation marks). None have given a working result.
I'll try and get some print screens too for more visibleness (new made-up word there) so you can see what I mean.
Hi, i'm quite new to Access and I have a problem passing data from one form onto another.
I have a form that I use for quotations based on the table T Quotations on it I have: QuotationID as my primary RefCust referring to which customer Date and a few other fields
Now i'd like to add a button that when clicked will open my order form. In other words when a quote is confirmed I'd like to click on the quotation form to confirm it and create an order on the order form. Order form will open with a orderID autogenerated BUT will return the same RefCust as on the Quotation form, the same date and will give the quotationID as Refquote onto the new form.
Ex: on form quotation I have Cust: abc company QuotationID: 123 Date: Feb 25 2007 Quote: $300
when i click on confirm
the Order form will open with: OrderID: 002 (autogenerated) RefQuoteID: 123 Date: Feb 25 2007 (even though today might be April 10) RefQuote: $300
I have a form called frmSchedule. Within this form, I have a combo box called cmbCD. I have code in the cmbCD NotInList Event. If the user inputs a number not in the list, the folowing code opens up a new form (frmPatients). The user can now add a new record to the database.
Code: Private Sub cmbCD_NotInList(NewData As String, Response As Integer) Dim Result Dim Msg As String Dim CR As String
CR = Chr$(13)
[Code] ....
I got this code and it works. However, when the user inputs a new number in frmSchedule, the NotInList event takes them to frmPatients. The users then have to re-type the number in frmPatients.
My question is: Is there a way I can trasfer the NewData from frmSchedule into frmPatients, so the users don't have to re-type the number?
I have a search form that searches for student ID from the student demographics table. I have it set when I click on the student ID (Frm_Student_Demographics_DailyCares) it will open the student visit form. When the student visit form opens it will show the student ID on the Student Visit Form but it is not being recorded in the student visit table.
I have Student ID control source on the Student Visit Form set as:
I have a form with a date field, when the user creates a new record, I would like the date field to automatically fill with the most up-to-date date from the Orderdate table.
Basically I need the code to do the following when a new record is created;
Search the Orderdate table for the most recent date and then auto fill the date field on the form with that date!!!!
My thinking so far...
Private Sub Command34 - where would you set this event on the properties i.e. Before update or On Got Focus ?
Docmd.OpenQuery "QryFindMaxDate"
I'm not really sure how to pass the date to the text box on the form,
I have a form that displays a stock inventory, one of the fields I use is called [ItemCode].
On the Stock_Inventory form I have 10 command buttons that open other forms to display additional product information.
These were previously sub_forms that sat in a tabbed control box within the Stock_Inventory Form but as it now takes a while for the form to load (lots of queries running each time you search for a product) users are getting slightly frustrated.
So, what I decided to do, was move each subform from the tabbed control into a new form and open that form when it is needed by the user.
OK, when I open Stock_Inventory and search for a product [ItemCode = "ABC123"] and then want to see the Sales_History, I have to open my new form, Stock_Inventory_Sales_History, within this form are two sub-forms, each subform has a field called [ItemCode].
However, when Stock_Inventory_Sales_History opens it asks me to input the parameter [ItemCode] twice, I presume this is because the Stock_Inventory_Sales_History does not have a record source and that the subforms need [ItemCode="ABC123"] to run each query and return the data.
I need to pass the [ItemCode] from the Stock_Inventory form to each of the 2 Subforms that are on the Stock_Inventory_Sales_History form automatically.
I have a rather annoying and frustrating problem which I cannot find a solution for through searching the forum.
The situation is that I have a parent form with 1 unbound subform, the source of which is determined by the part number selected in the parent form (diffetrent parts have different fields of the source table displayed)
This all functions perfectly well in all but one case which is when the source of the subform is a form with an option group.
There is only 1 form like this, and the problem I have is that when tabbing through the fields, as soon as I tab into the option group the parent form disappears, and I am left with just the subform on screen! Clicking into the group is fine, just tabbing to it causes the problem, and if I remove the option group then tabbing through all the rest of the fields is fine...
I could powssibly change the option group for a set of option buttons and code them to function a bit like an option group, but this would mean changing the code for numerous reports etc. and I don't really want to do that if I can help it.
In A2010 I maintain a database originally created in A2003. The original version was locked down tight. In A2010 I struggled to do the same but got there in the end, as well as allowing me a back door way in.
Now I have an unexpected problem - in the object browser (or whatever it's called) on the left hand side I can no longer copy any object. EG, I want to copy a form for safe keeping while I edit the original.
I would like passing values from first form until third form.
In the first form I have a list box after selecting items (For each selected item in first form I have 4 values) and pressing button (or right click of mouse) the second form will be open, then in the second form I have 2 option (inserting, deleting), when I select inserting or deleting in the second form, third form will be open, in the third form there is a "OK" button, when I press that, passed values from first form will be used for inserting or deleting records to the table.
Have one form that contains values AssociatedProject and AssociatedRelease that need to be passed onto another form that opens with a new record. Have tried different variations based on what I read here and couldn't get them to work.
Initial form - frm_ViewList contains the values that I need to pass on and has a "Add" button to bring up the new form that also creates a new record. The add button contains the following:
Dim stDocName As String MyAssociatedProject = Me.AssociatedProject MyAssociatedRelease = Me.AssociatedRelease stDocName = "Frm:ManageQuestionsAnswersProc" DoCmd.OpenForm stDocName, acNormal DoCmd.GoToRecord , , acNewRec
Then in the second form Frm:ManageQuestionsAnswersProc the following code is contained in the Before Insert:
I've got a parent/sub-form set up, both of which derive their data from linked tables connecting to a central database. As a result I've had to add coding to the NewRecord events to fetch the next primary keys from the database.
Unfortunately I'm finding that one loading the new record, the child form seems to be loading before the parent, ie the child form_Current event occurs before the parent form_Current event. Is this how it should be? It seems intuitive that the parent should load first.
I'm sure I can develop some cunning logic to get around this problem but I'm sure there must be a better way. Has anyone any experience of this parent/child set-up with linked, central database tables?
I have one parent table with 6 child tables (all child tables are linked to the parent table in the relationship through the BoardID autonumber field, and they are all one-to-one relationships). All of the tables feed into one form. I have added the "add new record" button to the form, but when I click on it, it only adds a record to the parent table. The only way I have found to solve this is to go into each individual table and add a new record. Is it possible to have a new record added to every table when I click the "add new record" button on the form?
I have a relationship between two tables; the parent table is "Client Information" with a primary key of "Client_ID", the child table is "Event Information" and the foreign key is "Client_ID".
I know that within the Event Information table I can select which Client_ID to use as the link. I want to have that done within a form, however. I have a form for Client Information with a subform for Event Information. If I go into the table and manually set the Client_ID then everything displays fine.
How can I set the Client_ID in the subform to automatically match the Client_ID in the main form? I can only assume that these solutions require the parent/child relationship to already have been established.
I have an unbound new contact data entry form that adds records to many different tables. I have no problems adding one record to a child table at a time, but one of the things we are collecting is a list of online networks a person belongs to, and I want to be able to stick a "check all that apply box" on my form and have it add multiple records to the child table. Here's the basic set up, I have a parent table with the main contact information with a primary key field "IID", a lookup table with "Facebook, LinkedIn, Twitter..." etc in it with a primary key field "online_id", and a bridge table to link the two which should have multiple entries for IID, one for each online_id. I want to use a listbox (or something like it) that the user can select multiple online networks and then have records added to the bridge table. I can figure out how to add the listbox on the form, what I can't figure out is how to get the values out of the listbox. T
I am updating an existing database and I have a parent/child form and I want to filter the subform for the current parent form record? The two forms are linked together by woWorkOrderID/deWorkOrderID.
Sorry this is so long but I am trying to make it as detailed as possible...
The Setup
My database is similar to the sample "Service Call" db. I have a main table called "tblTT" which has an autonumber primary key. The table also contains several foreign keys such as UserID (not an autonumber) from "tblUser", TechID (also not an autonumber) from "tblTech", etc. I have the relationships setup with "Enforced Referential Integrity" for both updating and deleting records. Each relationship has a RIGHT OUTER JOIN ("Join 3" in access) so that all records from child (tblTT) and only equal from parent (tblUser, tblTech, etc.) will be included. I have a form for nearly every table which serves different purposes but the main function of the DB is to create new Trouble Tickets (TT's), a.k.a. service calls. Therefore the main form used is my "frmTT" form in add mode. The form contains all the fields from my "tblTT" table and contains (directly) no fields from any other table (I guess indirectly it contains fields from all the parent tables...).
The Problem
When I pull up "frmTT" and try to create a new Trouble Ticket for a user that does not yet exist in the table "tblUser" I get the error "You cannot add or change a record bcause a related record is required in table 'tblUser'".
What I want is for my users (the "Techs") to be able to create a new Trouble Ticket without having to worry about populating the "tblUser" table (and other parent tables) first.
My Solution
I was going to (and unless someone can find an answer for me still will) fix this using VB script by setting up a query to check all the parent tables for the values in their corresponding fields in the form. If the query returns no results an "INSERT INTO" statement will run to populate the parents tables so that the form will save itself into the Trouble Ticket table ("tblTT").
I feel that this is a huge work around and not the proper fix. I would prefer to do this the right way both to have a correctly setup DB and for future reference. Can anyone help me with this?
How to structure some MS Access 2007 Forms for the following scenario.
My table structure is as follows:
Product ++++++ ID (pk) ProductName ...
Tag ++++ ID (pk) TagName
ProductTag +++++++++ ProductID (fk) TagID (fk)
I want to have a main form that allows me to search for and displays a list of all products (and the dozen or so attributes for each product) that meet that criteria (I'm thinking a continuous form view of products).
So far so good. No problems.
Now I want a "tagging" form to the right of my continuous forms product list that is linked to the product which currently has focus. The tag panel should always show a complete list of all Tags. I want a checkbox next to each tag that I can switch on and off whether that tag applies to the currently selected Product. I also want to be able to add tags on the fly (without leaving the main form).
I think I have three forms in play :
Main Form Product Form (subform to Main) my product search criteria is probably on this form header. Tag Form (also subform to Main)
But with this approach I'm have trouble linking the two sub forms to each other. Actually, I'm having huge trouble figuring out how to display the list of all tags with a check box that when checked on and off creates / deletes a record in the ProductTag table and I really want Access to do as much of the CRUD as possible. I would prefer to avoid writing screeds of SQL INSERTS/DELETES on events. Am I asking too much of Access?
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
I've been making small changes here and there to my database in Access 2013. I have left the original database in place and it has acquired many new records from the last month. I'm ready to start using the newly updated design version of the database, but I do not want to have to type all those new records into the new database to get it up-to-date. There has to be a way to import the data from the original database to the newly updated one.
Most, If not all of the fields have the same names, but some of the fields have changed, like to Combo boxes. I removed two or three unneeded fields, and added two or three. I understand the new fields will need to have data enter to them, but the remaining would be redundant and inefficient if I have to reenter all the recent records again.
How do I import the data from the original two tables to the new updated database? I only have two tables and they use a One-to-One relationship in both databases. The table's names are different, but as I mentioned earlier, the fields are mostly the same. Do I need to import into Excel, and then modify the data slightly, and then import into my new database?
I downloaded a sample treeview application I have modified most of its to suit my need, except one thing. I want link parent/child node the main form to the main form through a subform on the CategoryID.
The following is the original code where when a child not is clicked only pops up a "linked" msgbox. I need to modify the part of the code on the Select Case "C" in red:
Code: '---------------------------- Sub DisplayForm(i As Integer) '---------------------------- ' for this Treeview I do not want a double-click on a parent node ' to open the applicable Category form, but pressing the shift key ' should open the marriage form applicable to the parent node. Shift ' or double-click on a child node should open the applicable ' offence form.
Dim strKey As String Dim strTag As String Dim strFilter As String
I have a form and a subform in MS Access 2003. I have made some changes to database structure, so I decided to change the subform also. When I changed the Link child and link master fields, the controls of the subdatasheet dissapear- they show only in design view. If I clear the contents of Link child and link master fields they appear again, but the records are not binded. Is there a setting on the parent form that also has to be changed, to make the new binding?
I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.
I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".
The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".
These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.
I have attached 3 (.jpg)'s to show you what I am talking about.
