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.
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 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?
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 seem to be having a problem with acess 2003 in that when ever I set the parent/child relationships and go back to the front end the simple disappear?
where, in the case of a child, its ParentID contains the PersonID of its parent. In the Relataionships window I put two such tables, Family and Family_1 and I joined Family.ParentID to Family_1.PersonID. I set referential integrity, cascading updates and cascade deletes. I made a Parents form (by setting the ParentID criteria to Is Null), and added a Children subform (linking the ParentID of the subform to the PersonID of the main form).
The cascading updates works when I add a new child but when I try to delete a parent I get the error: Could not update; currently locked. Why doesn't the cascade delete work?
I have a main form with 2 subforms. Subform 1 shows the results of query 1, subform 2 shows the results of query 2.
Query 2 uses query 1 as a starting point. Query 1 is updated with an option group on the main form. In other words, when I select an option on the main form, qdef for query 1 is updated and query 2 will also be affected.
If I select an option the subforms don’t update. If I close and reopen the main form, the subforms show the correct information so the queries are being updated but the subforms are not showing the updated information.
I’ve been trying to requery the subforms using the following code in the OptionGroup_Click() event:
I have a field in the parent table which indicates how many child tables there should be at the most. I'm uncertain about how to enforce this. I"m using access as the database to a Vb.net app. Should I do it in access or Vb.net or both.
Hi I have a parent-child base table (accounts) with the following fields: - id - parentid - name every record's parentid is either 0 ( which means it is in the most upper level) or another record's id (which means it is the other record's child) different levels of records are being used. I mean some accounts are used in the most 1st level with no childs, some in the second level, ....
I need a query to list the accounts which are not parent to any othe records, regardless of what level they are defined.
I am creating a purchase request form for my company. The main form contains the PO Request # (autonumber) and general vendor and shipping information. The subform contains the item numbers on the order, the quantity and costs. What I am trying to do is make it so that users can click a "duplicate" button on the main form of an existing PO Request and have all the data replicated into a new PO Request. A lot of what we order is repetitious and it would time saving if users could just duplicate an order and modify as necessary. Can anyone help me with this one... Thanks!:confused:
I'm trying to create a capacity report for my database. Originally, I only had the one table, which summed the number of Packs and number of Images, worked out percentage capacity used and then put it in a report, showing for each week of the year. However since then, I've added a child table to this, allowing me to create subrecords. The reason we did this was so we could easily group together multiple mailings under one single master record, so to speak.
Going back to the capacity, I've managed to work it so the Pack capacity is worked out from the mailing quantity in the subrecord. However, the number of Images is only entered onto the Parent record. Now my capacity query is summing the same number of images as there are subrecords, where in reality I only want it to sum the Images in the main record. I know I could add an Images field to the child table and work it out the same way, but I'd rather not do that (because that's not how our business works).
So essentially the question is, how can I sum the Images from the parent table without repeating the sum and massively overexagerating the sum?
I need to refresh listbox field (Total_price) situated on parent form. My sub form is called "Lines_form". I want to sum values of all lines for column "price". Result of calculation I want see in parent form in field "Total_price". My parent form is called "Main_form".
On "price" field settings I used AfterUpdate event : Me.Parent!Total_price.Requery
I see result in parent form, but value isn't saved to table and I can't use it in next calculations.
I need refresh/requery/save patern form. I try this code:
I am trying to reference a field on a sub form from a button on the main form. I want to read a field called "Date1" and I want Date1 from the first row, regardless of which row has been selected by the user. The statment used is
and wanted to change this. I have this on the On Current event and it gives me an error because the data is not in the record source when the form opens. How can I change the me. to the formname? The form gets dynamically created so i have
Dim CurrentForm As String CurrentForm = Me.Form.Name
then i put
GBL_Tab05ID = Forms(CurrentForm).Form.Tab05ID
but that did not work...what is the correct syntax?
I have a table I'm trying to query information out of. Key fields are below:
RecNo (Key) ParentRecNo Description
I need to have a "record" in the query show both the description of itself, as well as it's parent. I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.
I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record. I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:
Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.
'------Code I'm Currently Working On------- Dim intX As Integer Dim EventVal As Integer Dim strX As String
intX = 1 EventVal = Val(Mid(Event_List, 7, 1)) Do While EventVal <> intX And intX < 6 intX = intX + 1 Loop
strX = str(intX) Dim E_Name As String Event_Name = [Event strX ] PMT = [PMT strX] Receipt = [Receipt strX] Payment_Type = [Payment Type strX] PIF = [Paid In Full strX]
'-----Old Code That I'm trying to simplify------- If Event_List = "Event 1" Then Event_Name = [Event 1] PMT = [PMT 1] Receipt = [Receipt 1] Payment_Type = [Payment Type 1] PIF = [Paid In Full 1] Camp_Site = [Camp Site 1] Cabin_Number = [Cabin Number 1] ElseIf Event_List = "Event 2" Then Event_Name = [Event 2] PMT = [PMT 2] Receipt = [Receipt 2] Payment_Type = [Payment Type 2] PIF = [Paid In Full 2] Camp_Site = [Camp Site 2] Cabin_Number = [Cabin Number 2] ElseIf Event_List = "Event 3" Then Event_Name = [Event 3] PMT = [PMT 3] Receipt = [Receipt 3] Payment_Type = [Payment Type 3] PIF = [Paid In Full 3] Camp_Site = [Camp Site 3] Cabin_Number = [Cabin Number 3]
In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.
So for example
112 - Main Road
If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)
The continuous form cannot be edited, it is to be a list for viewing the information only.
I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.
I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.
I tried the DoCmdSearch for record and just keep getting object is not open errors.
I have a listbox on a subform (or a subform within a subform).
When it is clicked I want all other listboxes to unselect.
There may be listboxes on the mainform, on other subforms, on other subforms of subforms.
I imagine I'm looking at a recursive function of some sort, but I'm not entirely sure of syntax to identify parent and children forms...
pseudocode so far: loop all controls if control = listbox, unselect all if control = subform - recurse: loop all subform controls if control = parent... err... Fail.