I don't have experience in programming in Access or generally VBA, so I need to ask you this:
I'm creating a new DBase in Access 2007, for an insurance office. I have created 3 levels of tables:
Customers (grandparent), Contracts (parent), Payments (child). The correspondent forms contain the related subforms. I'm sending you the Access 2007 file to check it out.
What I 'm looking for, is to create a button in the parent form (eg CustomersBrowse), that will create a new child record by form use (eg ContractsNew). What I actually need is when the ContractsNew form opens, to inherit the ID_Customer (of the parent form) in the Customer field (of the opened child form).
Is there a way to do it?
(I 'm sorry for the greek language in the DBase, it's my mother language)
You must first open the form before you can reference that form:
Expand|Select|Wrap|Line Numbers
Private Sub Command15_Click()
DoCmd.OpenForm "ContractNew"
Forms![ContractsNew].Customer = Me.ID_Customer
End Sub
However, the Query behind your Combo Box for the Customer is invalid, as it is referencing fields that do not exist in your Customers table. You must fix this first.
Garfield, Yes, what you are asking for is very possible. The easiest solution I can provide is simply that if you create your Forms correctly, this should be automatic.
Your main form would have the Customers table as its record source. One of the fields should be ID_Customer. On the First subform, just make sure that the forms' Master-Child field is ID_Customer. If this is the case, every time you create a new contract for the Customer, the Customer ID should translate to the new Table.
Likewise for the Payments Subform (which I assume will be embedded int he Contracts Subform. This should do the trick.
I guess with the ease of use of using form/sub-form design, I would have to ask if it was necessary to use a separate form. Yes, it is possible to do it that way, but if you don't have experience using Access and VBA, then that is not the place I would start. Even with experience, I would still recommend the form/sub-form construction, simply because of its ease, and there is no "necessary" VBA to make it work.
We can work toward doing it however you desire, but it depends on how comfortable you are with complexity.
How difficult can it be?
I ve tried macrobuilder in Access, but couldn't locate the right command queue of commands (I couldn't set the parent ID value as foreign key in the "new child record" form).
The reason why I seek this way, is that the DBase is going to be used by my father (very novice user!), an insurer. So I want to ensure the data security of the working DBase as much as possible.
There is no advantage to using a more complex method. The basics would be to identify the index, then send that index to the new form and filter the form by it. If it is going to a new record, then you build the pop up form to open that way, also.
I always lean toward the easier method of DB development unless there is an absolute need to make it more complex.
The thing is that I 've already build the popup "new child record" form (ContractsNew + PaymentsNew) -> DataEntry form property = Yes.
My problem is that I can't make it open with the parentID (foreign key) already set in the new record popup form!
I ve tried build-in automated ways (eg Command button wizard's "Add new record" option) & through the macrobuilder setvalue command (real alchemy there, but failed!)... None of them manage to make it work!
When you fire the code to open the form (I'm assuming in VBA), add the following line:
Expand|Select|Wrap|Line Numbers
Forms![your child form name].txtyourcontrolname = Me.txtID_Customer
What this does is after the new form opens, it assigns the value of the current record's ID_Customer. Since your form is opening in data entry mode, this will automatically be a new record, as you desire, and this code should establish the customer ID also as desired.
TxtID_Customer is the name of the control on your form that holds the customer ID. I do not know the name of your control, so replace that appropriately.
What I m trying to do is create the "New contract" button on the "CustomersBrowse" form,
which will lead to the "ContractsNew" popup form (in DataEntry mode). The parent field is "ID_Customer" in the parent form "CustomersBrowse" + the foreign key is the "Customer" field in the child form "ContractsNew".
You must first open the form before you can reference that form:
Expand|Select|Wrap|Line Numbers
Private Sub Command15_Click()
DoCmd.OpenForm "ContractNew"
Forms![ContractsNew].Customer = Me.ID_Customer
End Sub
However, the Query behind your Combo Box for the Customer is invalid, as it is referencing fields that do not exist in your Customers table. You must fix this first.
I haven't seen this solution of yours!
I 've seen it now & worked like charm!
Thanks a lot man! You re the best!
(thanks for your patience with a novice)
Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.
The parent form (frmMainForm) displays the...
Thanks for the replies to my previous post. So while I'm on a roll
here's another plea for help.
I simply need the code for a button on the main form that will create a
new record in the...
I have a form with a child form. In the child form there is a list of
names that can grow quite large. On the parent form I want to display
the first name from the child form. I set up a test...
Hello,
I want to open a child form based on the record selected in a
dataGridView of the parent form.
I want the child form to open with a populated child table based on the
selected parent...
Hope someone can help.
I have a saved parent form containing an unbound sub-form. I set the
SourceObject (form) after the Parent Form opens/loads.
The sub-form (datasheet view) basically...
I have a simple form with 4 fields, and a subform that retrieves records that are potential matches based on a query.
The user will use the ID from the subform record and enter it into one of the...
hi i have a multiline textbox and a button in parent form. in the child form i have text boxes,comboboxes,button. the thing is i click the button in the parent form ,the child form opens. i fill the...
Dear expert;
using access 2003 .
I have a table name "tables" that contains "tableid" & "tabledesc"
The idea is to design a form that contains for each record in "tables-->tableid" a...
Hi all,
I have two tables with a many-to-many relationship which is causing many problems. My parent and child forms are linked via a junction table. I want to make it possible to create a...
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...