Create button for new record in parent form

[garfield314]

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)

[twinnyfo]

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.

[garfield314]

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/answers/560576-passing-master-autonumber-id-subform-new-record

[twinnyfo]

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.

[garfield314]

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.

[twinnyfo]

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.

[garfield314]

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!

[twinnyfo]

When you fire the code to open the form (I'm assuming in VBA), add the following line:

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

[garfield314]

Nop! It didn't work!

This is the result screen:
(I must done something wrong!)

[twinnyfo]

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.

[garfield314]

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 "CustomersBrows e" form,
which will lead to the "ContractsN ew" popup form (in DataEntry mode). The parent field is "ID_Custome r" in the parent form "CustomersBrows e" + the foreign key is the "Customer" field in the child form "ContractsN ew".

Have a look if you wish.

[twinnyfo]

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

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.

[garfield314]

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![ΣυμβολαιαNe w].Πελατης = Me.ID_Πελατη?

Use of another language characters effects Access?

[twinnyfo]

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.

[twinnyfo]

I don't know if unicode works with Access, but you can try.....