473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Create button for new record in parent form

Hello!

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)
Attached Files
File Type: zip Insurance office.zip (142.5 KB, 281 views)
Jul 2 '14 #1

✓ answered by twinnyfo

You must first open the form before you can reference that form:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2.     DoCmd.OpenForm "ContractNew"
  3.     Forms![ContractsNew].Customer = Me.ID_Customer
  4. 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.

17 8071
twinnyfo
3,653 Expert Mod 2GB
Kaire!

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.

Let us know if you come across any snags.
Jul 2 '14 #2
I know that it was possible through the subform (before I use Access, I was using FileMaker - it was quite easier in scripting).

Is there a way to do it through a popup form, called by a button?

I 've found some ways, but I can't adjust them to my DBase.
http://bytes.com/topic/access/answer...orm-new-record
Jul 2 '14 #3
twinnyfo
3,653 Expert Mod 2GB
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.
Jul 2 '14 #4
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.
Jul 2 '14 #5
twinnyfo
3,653 Expert Mod 2GB
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.
Jul 2 '14 #6
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!
Jul 2 '14 #7
twinnyfo
3,653 Expert Mod 2GB
When you fire the code to open the form (I'm assuming in VBA), add the following line:

Expand|Select|Wrap|Line Numbers
  1. 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.

Let me know how this helps.....
Jul 3 '14 #8
Nop! It didn't work!

This is the result screen:
(I must done something wrong!)
Attached Images
File Type: jpg 1.jpg (31.8 KB, 335 views)
Jul 3 '14 #9
twinnyfo
3,653 Expert Mod 2GB
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.
Jul 3 '14 #10
Same error!

I m sending you the 2 error screens following +

my DBase file.

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

Have a look if you wish.
Attached Images
File Type: jpg 1.jpg (20.2 KB, 258 views)
File Type: jpg 2.jpg (31.8 KB, 274 views)
Attached Files
File Type: zip ENtest.zip (150.3 KB, 103 views)
Jul 3 '14 #11
twinnyfo
3,653 Expert Mod 2GB
You must first open the form before you can reference that form:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2.     DoCmd.OpenForm "ContractNew"
  3.     Forms![ContractsNew].Customer = Me.ID_Customer
  4. 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.
Jul 3 '14 #12
Another question:
Is there any problem if I use unicode language in objects?

eg instead of

Forms![ContractsNew].Customer = Me.ID_Customer

I use
Forms![ΣυμβολαιαNew].Πελατης = Me.ID_Πελατη?

Use of another language characters effects Access?
Jul 3 '14 #13
twinnyfo
3,653 Expert Mod 2GB
I am also having problems running your DB. It should work as I am using 2010, but your form is not behaving well for me.
Jul 3 '14 #14
twinnyfo
3,653 Expert Mod 2GB
I don't know if unicode works with Access, but you can try.....
Jul 3 '14 #15
Anyway, is there a way to use the macro command "SetValue" to do this trick? (I mean auto-fill the foreign key in the child form)
Jul 3 '14 #16
twinnyfo
3,653 Expert Mod 2GB
I would recommend you try to accomplish this through VBA. It is the best way to manage your objects in the long run.
Jul 3 '14 #17
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)
Jul 3 '14 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

25
by: Lyn | last post by:
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...
2
by: Deano | last post by:
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...
5
by: tdmailbox | last post by:
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...
2
by: dbuchanan | last post by:
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...
5
by: superjacent | last post by:
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...
7
by: dscarbor | last post by:
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...
1
by: jiaudheen | last post by:
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...
33
by: Dev1 | last post by:
I'm triying to create a new record via a macro, like so: MACRO NAME ACTION CreateNewX : On Click Go To Record Object Type...
5
by: wassimdaccache | last post by:
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...
20
LeighW
by: LeighW | last post by:
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...
0
by: DolphinDB | last post by:
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
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...
0
by: Vimpel783 | last post by:
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...
1
by: CloudSolutions | last post by:
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
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
0
isladogs
by: isladogs | last post by:
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.