Form that adds data to a table depending on which option was clicked in combo box

[Roku]

To handle records when you switch between them (first/last or any other) you can use the Form's Current event to carry out any specific actions.
For the subform to show the person associated with the current customer, you need to set the parent/child relationship in the subform control. By linking the foreign key field in the main form to the person primary key in the subform, it will work as if by magic.

 

[Bilal]

For the subform to show the person associated with the current customer, you need to set the parent/child relationship in the subform control. By linking the foreign key field in the main form to the person primary key in the subform

im not really sure about this, ive tried using 'link master fields' and 'link child fields' keep getting an error saying 'can build a link between unbound forms'

secondly, what would my 'foreign key field' in the main form be and how do i get to it, lol i know this might be the stupidest question youve been asked. and when you say the person primary key in subform would they both be textboxes?
because ive got two textboxes, one in my subform and one in main form, they also have the same name and i think thats what i need to make connect, if you see my attachment you will see the two textboxes that are circled red.

 

[Roku]

Hi

Best illustrated by an example - see attached.

The main requirement of linked forms is that they are bound to a table. In my example, there are two very simple tables which are linked by the foreign key (ctParent) in tChild. This FK holds the key to table tParent, which is how the link is made.

There are two forms; one parent and one child (fParent, sfChild respectively). In the parent form, there is a subform control (sbChild) whose Source Object is sfChild. 'Link Master Fields' has the primary key of the main table and 'Link Child Fields' has the foreign key which points to the parent record (via its key).

I included Form_Current event handlers for both parent and child forms. As you step through the parent and child records, see what the handlers do to the message on the main form (lblText).

To give you an idea of the effort involved to create this example, I spent about five minutes mapping out the tables and forms, then about half an hour building and testing. This is by no means a 'robust' database (no error-handling etc.), but it's quite a simple process once you know how (just like riding a bike ).

You might find it useful to start with an abstract structure like this to explore possibilities. Once you understand them, you can apply the principles in your application.

Have fun!

 

[Bilal]

http://images.access-programmers.co.uk/forums/images/smilies/banginghead.gif
http://images.access-programmers.co.uk/forums/images/smilies/banginghead.gif

thank you very much for that database example you took time out to create for me,
I have three questions..
1. how come there is like one line of vba code
2. how come you didnt set any relationships up
3 if your FK is called ctParent in your tChild Table, shouldnt you have ctParent also in the tParent table

on my current database im getting error after error, if you see it, my suppliers table is fine however if you open the suppliers form you can see that the data of the first record has been duplicated 8 times , this whole main form and subform business has been the most irritating thing ive come across in the 3 weeks its took me to create this whole thing (lol yes, 3 whole weeks)

the main problem has come from that fact that i lifted code from a similar form/subform, however i was creating/deleting/updating code that i was unsure about right from the beginning.

if you can sort out the code or the form itself i would be realy grateful, this is not my actual database so il need to know what changes you have made, or even better i can just copy and paste the table, form, vba from your edited to mine
http://images.access-programmers.co.uk/forums/images/smilies/banginghead.gif

 

[Roku]

1. how come there is like one line of vba code

It's just a simple illustration of the event handling. The code itself is completely irrelevant to the linked tables, in this instance.

2. how come you didnt set any relationships up

I take it you mean in the Relationships manager? I very rarely use this nowadays (I did when I first started using Access many years ago, but it's not necessary for most things).
In my example, the relationship is explicit in the linked fields for the subform.

3 if your FK is called ctParent in your tChild Table, shouldnt you have ctParent also in the tParent table

No. ctParent in the child table is simply a pointer to the primary key (ptKey) of the relevant record in the other table involved.

I can't spend time on your DB today, but I'll try to have a look tomorrow. In the meantime, someone else might take a look and advise.

 

[bob fitz]

Bilal

Can you post a copy of your db in A2003 mdb format

 

[Bilal]

Bilal

Can you post a copy of your db in A2003 mdb format

hi, sorry for the late reply, here is the mdb format of my database

 

[Roku]

I have added two new forms to your DB; 'fSuppliersNew' and 'sfPersonNew'.

This pair of forms simplifies the task you want, but it still lacks the means to add a new person when creating a new supplier. I'm struggling to understand your business model, as you appear to have a one-to-one relationship between supplier and person. My expectation for this kind of relationship is many-to-many; i.e. a supplier can have many people and a person can belong to a supplier, a customer or an employee. In my thinking, I would have pointers from person to supplier/customer/employee, rather than the other way around as in your model.

I found you code very hard to understand. I strongly recommend the use of comments, because you will find you don't understand it yourself in six months!

The key thing I did was to bind the forms to their tables. By this, I mean the Form RecordSource has the table name and the form controls are then bound to individual fields in the table.

The link between parent and child froms is the PersonID field (the foreign key in the parent and primary key in the child). If you create a new supplier record, you will get an error when entering the subform - this is because the PersonID is null at this point. As there is no related person at this point, it's not straight-forward to add the person record here (but no impossible). I played around with a few methods to create a related person, but none of my techniques were satisfactory. If another member can solve this easily, I'd love to see their solution!

I placed record selectors in the form footer for the parent and used a common handler for the Click event on each. The individual control passes a byte value to the function (values match acFirst, acNext etc.) which determines which action is taken. I added code to disable the 'Previous' button when the first record is encountered (but this is not robust).

There is very little code in these forms; much of what you coded is handled automatically within bound forms. There are some functions which are not handled from your code, but I didn't attempt to replicate them as I didn't understand the purpose.

I have changed this version to use overlapping windows and set fSupplierNew to open when the DB opens.

Anyway, have a look at this and see where it takes you.

 

[Bilal]

I really appreciate your help. this really helped to take me towards what i wanted to do, i was slightly confused because the add,delete and save button were quite important im not sure how to add that on now, but it has helped me work on another route (im trying to puzzle together al the basics of access) i can do a bound form, quite easily, the whole unbound form is an academic exercise to get my head around them. what ive been doing for sometime is creating something and not always going for the easy option and taking it the hard way.
thanks for all your efforts
Bilal

 

[Roku]

Here's a version which includes the buttons. I put them in the form header, but that's just a convention I use and is not important to the solution.

Since my post yesterday, I thought of a way to link a new person to the supplier table when creating a new supplier. This works by showing either a combo box for new supplier records or the subform for existing records. The combo box includes NotInList handling for the name, which shows one way of implementation (there are other ways). There's more code in this version.

There is nothing wrong with doing things the hard way - a great way to learn. However, if you post questions here, it would be advisable to say that you know your approach is non-standard, otherwise members will tell you their 'right' way, rather than addressing the actual question you have.

Have fun!