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

[Bilal]

Hi i am a beginner at access, fairly new, understand some of the basics. i have a standard database with tblProducts, order, employees, customers, suppliers etc. i also have a table called tblperson with personID which acts as a one, with the many in three different tables (customers, suppliers and employee) the person table holds data such as first name last name address city postcode and phone. now, ive created a form called frmPerson that acts as a adding data to my database. ive got a load of buttons a working add, delete and a save button, i also have the option to switch through records next, last, end and first. now basically in this form i want to be able to add data i.e. fill out the text boxes and then have a combo box or something similiar that when i add the data i can basically click an option the says 'employee' 'supplier' or 'customer' and then the data gets added to my person table however the difference is that it will also be associated to the record in the wanted table, any help please?!! been stuck on this part for ages

 

[Roku]

Hi
I don't understand what you want to achieve from your combo box?
Is the form which contains the combo box bound to the person table and does that table get updated with the value you want from the selection?
What exactly do you mean by

the difference is that it will also be associated to the record in the wanted table

Presumably you want the foreign key in each of the other tables to be updated to point to the newly-added person record?

 

[Bilal]

Presumably you want the foreign key in each of the other tables to be updated to point to the newly-added person record?

yes that is exactly what i want except, say i add john smith to my DB with his full address, city etc however he is a supplier and i want to add him as a supplier, i will want his data to be assosiated via PK to the suppliers table, not customers and not employee table, then next, say i want to add mohammed bilal as a customer same as above but i want to be able to click supposedly a combo box in a subform (most likely, maybe main form) that has 3 options; supplier, customer, employee. once one of these three are clicked it should only link to its desired field (im really sorry if im using incorrect terminology and confusing you, but please do correct me if you feel im using the wrong words)

 

[bob fitz]

Perhaps it would be better to have just one table to hold data of each person with an extra field that denotes if they are an employee, customer or supplier.

 

[Roku]

OK, let's focus on those linked tables. Presumably there are other fields in them, so you need to add the FK (foreign key) to person as part of a complete new record for whichever table is the target?
You could try using DoCmd.OpenForm to open the relevant form for the required table, passing the FK value as an OpenArg (this will be the string value of the FK). It would be the Change event on the combo box which triggers this, with the form name selected according to its value.
When the target form opens, check the OpenArgs to see if a value is present. If it is, put the the string (converted to Long) in the form field for the FK.
I'm not elaborating further at this point, as I need to know if the approach is valid to your needs. If you like this idea, I can provide sample code to get you going. If you want something different, please provide the outline of the process you want.

 

[Bilal]

Perhaps it would be better to have just one table to hold data of each person with an extra field that denotes if they are an employee, customer or supplier.

yes that is exactly what i have right now however the extra field makes it a bit tricky, i do not know how i will execute it. so you think this is the best way to do it?
the way im thinking of your advise id that i can easily have a combo box with the options listed and you can click away or even a text box where the label will be 'what type of person?' and the person fills out 'customer' 'supplier' 'employee' but ones it enters the table tblPerson there will be a field that says type of person and as you look down you wil be able to see yes this guys an employee oh shes a supplier and so on, but how will you be able to get that employee or that supplier record to be linked only to the employee table or the supplier table.
i dont know it may be really really simple but i could just be over confusing myself

 

[bob fitz]

yes that is exactly what i have right now however the extra field makes it a bit tricky, i do not know how i will execute it. so you think this is the best way to do it?
the way im thinking of your advise id that i can easily have a combo box with the options listed and you can click away or even a text box where the label will be 'what type of person?' and the person fills out 'customer' 'supplier' 'employee' but ones it enters the table tblPerson there will be a field that says type of person and as you look down you wil be able to see yes this guys an employee oh shes a supplier and so on, but how will you be able to get that employee or that supplier record to be linked only to the employee table or the supplier table.
i dont know it may be really really simple but i could just be over confusing myself

What data fields do you have in the other tables (eg Customer)
that you don't have in the person table.

 

[Bilal]

OK, let's focus on those linked tables. Presumably there are other fields in them, so you need to add the FK (foreign key) to person as part of a complete new record for whichever table is the target?
You could try using DoCmd.OpenForm to open the relevant form for the required table, passing the FK value as an OpenArg (this will be the string value of the FK). It would be the Change event on the combo box which triggers this, with the form name selected according to its value.
When the target form opens, check the OpenArgs to see if a value is present. If it is, put the the string (converted to Long) in the form field for the FK.
I'm not elaborating further at this point, as I need to know if the approach is valid to your needs. If you like this idea, I can provide sample code to get you going. If you want something different, please provide the outline of the process you want.

thanks very much for your time and effort with this long reply, just to let you know lol i read your paragraph about 5-6 times trying to understand what you meant because of your jargon, i think i get what you are saying my relationships with foreign key and primary key are all set and robust. im very interested in the 'change event' that you speak of, never heard of it but it seems like something i am going to need. below is a doc of screenshot of my relationships model along side my tables, queries, forms. thank you again for your time

 

[Bilal]

What data fields do you have in the other tables (eg Customer)
that you don't have in the person table.

Attached: screenshots of customer, supplier, employee and persons table. its not many fields just a few

 

[Bilal]

OK, let's focus on those linked tables. Presumably there are other fields in them, so you need to add the FK (foreign key) to person as part of a complete new record for whichever table is the target?
You could try using DoCmd.OpenForm to open the relevant form for the required table, passing the FK value as an OpenArg (this will be the string value of the FK). It would be the Change event on the combo box which triggers this, with the form name selected according to its value.
When the target form opens, check the OpenArgs to see if a value is present. If it is, put the the string (converted to Long) in the form field for the FK.
I'm not elaborating further at this point, as I need to know if the approach is valid to your needs. If you like this idea, I can provide sample code to get you going. If you want something different, please provide the outline of the process you want.

is it possible that i send you the whole database and i run ou through what i want, you dont necessarily need to do it for me, that way you know what i need, what im missing, and you as the expert can point out my mistakes?

 

[Roku]

Yes, happy to do that.

 

[Bilal]

Yes, happy to do that.

have you got an email address i can post it to? because it exceeds the limit?
thaaaanks

 

[pr2-eugin]

because it exceeds the limit?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Bilal]

Yes, happy to do that.

here is the database im working on

 

[Roku]

I've had a look at your DB alongside the map you sent previously and I think your approach is inappropriate for what you want to do.

I would suggest your primary actions will be focused on the employee, customer and supplier tables, with the person table simply an input to them.

When you add a new record to one of those tables, you will either create a new person or link to an existing one.

As a general rule, every form should be bound to a datasource. There are cases where one would use unbound forms, but not in your situation - so bind the Person form to its table.

Instead of using Text Boxes on your forms, consider using Combo Boxes to select from pre-defined lists. So the Employee PersonId would be in a combo box with its data source as a query to the Person table. You can construct the Drop-down list to show Lastname, Firstname. If you type a name which is not in the list, the NotInList event will trigger (providing the combo box properties are set correctly). From the NotInList event, you would open the Person form and populate the record as required. When the form is closed, you use the newly-added record as the data source for the combo box. You can find examples of how to do this in Access VBA help.

I think the way your forms are designed does not help your business process. It appears as if they are simply a view of the table, with no enhancement - by this I mean the PersonId (for example) is shown as a number, where the actual name would be more useful. Again, the combo box will solve that problem.

I suggest you start by describing your business process in words (avoiding technical jargon entirely). Describe what needs to happen when a new customer is to be added to your database (e.g. 'Customer detail to be captured, primary (secondary/tertiary) contacts names to be assigned ...' etc. By doing this, you will start to map out the logical flow and structure of your DB.

When considering your table design, think about the attributes in common between customers, suppliers and employees and list them as a group. For each type, list the unique attributes as separate groups (I'm talking about pencil and paper here). This might give you a different approach to your table structure, where you might combine suppliers and customers, for instance (but you might not).

I have amended your customer form to change the PersonId to a combo box to illustrate that basic method. Try doing the same for your other tables to understand the mechanism. Pay careful attention to the properties of the combo box in the Data and Format tabs. I haven't included the NotInList function here, but as I said earlier, you should find help on this elsewhere (it's just about knowing what you want).

I don't know how helpful this is and it might seem a bit daunting. If you can break your problem down into steps, then this community will help with each step.

 

[Bilal]

I've had a look at your DB alongside the map you sent previously and I think your approach is inappropriate for what you want to do.

I would suggest your primary actions will be focused on the employee, customer and supplier tables, with the person table simply an input to them.

When you add a new record to one of those tables, you will either create a new person or link to an existing one.

As a general rule, every form should be bound to a datasource. There are cases where one would use unbound forms, but not in your situation - so bind the Person form to its table.

Instead of using Text Boxes on your forms, consider using Combo Boxes to select from pre-defined lists. So the Employee PersonId would be in a combo box with its data source as a query to the Person table. You can construct the Drop-down list to show Lastname, Firstname. If you type a name which is not in the list, the NotInList event will trigger (providing the combo box properties are set correctly). From the NotInList event, you would open the Person form and populate the record as required. When the form is closed, you use the newly-added record as the data source for the combo box. You can find examples of how to do this in Access VBA help.

I think the way your forms are designed does not help your business process. It appears as if they are simply a view of the table, with no enhancement - by this I mean the PersonId (for example) is shown as a number, where the actual name would be more useful. Again, the combo box will solve that problem.

I suggest you start by describing your business process in words (avoiding technical jargon entirely). Describe what needs to happen when a new customer is to be added to your database (e.g. 'Customer detail to be captured, primary (secondary/tertiary) contacts names to be assigned ...' etc. By doing this, you will start to map out the logical flow and structure of your DB.

When considering your table design, think about the attributes in common between customers, suppliers and employees and list them as a group. For each type, list the unique attributes as separate groups (I'm talking about pencil and paper here). This might give you a different approach to your table structure, where you might combine suppliers and customers, for instance (but you might not).

I have amended your customer form to change the PersonId to a combo box to illustrate that basic method. Try doing the same for your other tables to understand the mechanism. Pay careful attention to the properties of the combo box in the Data and Format tabs. I haven't included the NotInList function here, but as I said earlier, you should find help on this elsewhere (it's just about knowing what you want).

I don't know how helpful this is and it might seem a bit daunting. If you can break your problem down into steps, then this community will help with each step.

thank you so so much for everything and i deeply thank you from the bottom of my heart for the long replies and the time you are taking out for somebody you dont even know , ive been going through everything you said and have been amending my table the past 45mins,
ive come to a slight problem i dont the whole combobox thing for each of the three tables, however they dont seem to be linking with the personID, for example where i have customerID, there are 7 customers, and the records run from 1-7 this is okay and is working well because personID is also 1-7 however for my employee, the employeeID is 1-6 however the personID is 8-13 and same with the suppliers where the SupplierID is from 1-7 however the personID is from 14-21.
from all this the problem is that the names in the combo for each of the three start with the same name and go down either 6 or 7 records, they do not start from their desired personID number.
Again if you have an email address i can send you my whole database and you can asses my problems and give my pointers and little tips and tricks etc

Thanks a bunch!
Bilal

 

[Roku]

Bob fitz' comment yesterday would apply in this case. If you do need to differentiate between person types within the table, add a category field and give them unique values. However, that isn't strictly necessary, as each table will point to its own person record. The category becomes relevant if you want to restrict the combo box selection to a specific subset of person records (which you seem to want).
If you look at the combo box I added, simply include the extra 'category' field in a WHERE clause on the query, and specify the value you want (say 1=customer, 2=employee etc). The field in the person table would have 1 for customer, 2 for employee ...).
I can't do the modification in your DB at present, but I'll do it later, unless someone else does so in the meantime.
I don't recommend publishing eMail addresses in this forum. Use the procedure from pr2-eugin yesterday to submit your DB as a zip file.

 

[Roku]

Sorry, I forgot to add 'Don't quote the entire original reply in your responses - it takes up too much space in the threads'

 

[Roku]

Hi
I looked at your forms again and see the problem. I put the combo box on the wrong field (CustomerId instead of PersonId). I have amended the customer form to rectify my error.
I have also added a table (tblType) to illustrate how you might use is as a reference. In this I have put three values (in no particular order, because that's how it will be in reality).
In tblPerson, I have added field ptType, which contains the foreign key to the new Type table.
Finally, I added the combo box to the person table to allow you to select the specific type you want. The table will store the type key value (1,2,3), but the combo box will show the text from the type (Customer, Supplier, Employee)
You don't really need this type table in your current design, but it may be a useful technique for future reference.

 

[Bilal]

I put the combo box on the wrong field (CustomerId instead of PersonId). I have amended the customer form to rectify my error.

Hi, thank you very much again, i completely scrapped this idea and thought of a different approach to it which is below in doc file(im the type of person to test new waters). How it works is that when i switch records i.e. click first record, last record etc the main form will show what it has to show normally but the sub form underneath that, will bring up the information from the Person table of the current record, im not really sure how im going to get it to work, i lifted some vba code from someones form that looked very similiar but i do not know where the amendments will have to be made, ive used the 'replace all' in the vba code to change all property names to ones that i have named but if you want i can show you that form.

Finally, I added the combo box to the person table to allow you to select the specific type you want.

this is a very good approach to solve the whole employee, supplier, customer problem , however one argument is that i was told by someone that the information in the peron table wil be exactly the same for everyone except the autonumbers (this question isnt really important atm, the top one is much more important, however if you have time please reply to this also