Linking forms/tables together?

[PhilD]

Hi
Hopefully someone can help me here. been struggling with this for a while.

Here's the brief!!
I have a access 2000Db, In it are are 2 tables, Tbl_Main & Tbl_Customer. and two forms frm_main & frm_customers based on the tables.

I have created a form called Form_main On this i have created a button which opens the form, Frm_Customers.

I want to link each record in fom main with form customers, basically so when i click on the button in form main it opens the correct corresponding record in frm_customers

Unfortunately when i scroll through the frm_main and select the customers button the customers details dont follow with each record. it only shows last record. Each record in the main form should follow with record from Customers table

I have created a relationship 1 to 1 so the data would follow each record but no avail.

where am I going wrong - If i can avoid code then that be fantastic as it confuses me!

 

[Liv Manto]

make the second for a subform and link them.

or check the command button wizard open form, it will ask you what criteria you want.

 

[PhilD]

tried this it still doesnt link the two sets of data together from the tables..

I've a customer Id refernce from tbl_customer and a Merchant Id from tbl_main.

i've tried to crete a relationship between both tables on a one to one.

data sill doesnt move when i move between the records, the dat ain customer stays the same.

 

[neileg]

It's not clear what is the linking data between the tables. To link two tables in a one-to-one or a one-to-many relationship, you need to hold the primary key of the relevant record from the first table as a foreign key field in the second. Like this:

tblMain
MainID Primary key
CoName
Address1
etc

tblSecondary
SecID Primary key
MainID Foreign key (holding the ID from tblMain of the corresponding record)
Data1
Data2
etc

Give us you table details like this and we'll understand.

 

[PhilD]

Thanks for the reply.

So far i've got

In Tbl_main

My primary key is customer number which is an autonumber.

In Tbl_Customers

My primary key is customer id.(autonumber)

No corresponding keys in either table. (will I have to create another key in one of the tables?)


I'm at a loss really how to join the tables together as i'm hoping to by able to click on a cmd button on Frm_main which opens the frm_customer.

I'd like the tbl_main data to correspond with tbl_customers data.

so when you click on the customer button o fom_main then the customer details appear. When you move to next record in frm_main then that customer details appear when you hit the cmd button.

Soory about not being very clear.. hope this is more helpful??

I have no relationships created between Tbl_main & tbl_Customer.
not sure if i need to create any.

ie.

 

[PhilD]

ohh, forgot to add this..

Tbl_main

merchant ID (primary key)
merchant name
company nos
account opened

Tbl_customer

customer id (primary Key)
customer name
address
town
post code

 

[neileg]

Just to expand from what Pat has asked:

Imagine you have a room full of people, some are adults and some are young children. You could easilly separate the adults from the children, but if I asked you to identify which adults were the parents of each child, how would you do this? You can't, can you?

Now if I tell you that each child is wearing a badge that has the name of their parents, and each adult has a badge with their name on, it becomes an easy task to link parents and children. That's how the key fields in your table need to work.

 

[PhilD]

Neileg, Pat

thanks for that, guess i better try to expain this better.

The result I am after is on the frm_main I want a command button which pops up another frm_customers where all the customer details are. (name, address, town)

I also want to make it so you can't scroll through the various records on this form.

When you close the frm_customers down and move to the next record on the Frm_main and hit the command button again, the frm_customer appears with a different customers details in it linked to the that record in the Frm_main.

So Each record selected in Frm_main brings up a different customers details

For Example

Merchant 1 - Mr Smith, 12 The View

Merchant 2 - Mr Blogg, 45 park row

I have created 2 tables but if it's easier just using one then that is fine. At the end of the day i just would like to know the easiest way to go about getting the end result.

The primary key in Table is called MerchantID


Hope I have explained this better

Phil

 

[neileg]

But how do you know that Merchant 1 and Mr Smith are related?

Once we get that sorted, you'd be better off with a form and sub form so you dont have to keep clicking buttons, but that's the easy bit.

 

[neileg]

On second thoughts, have a look at the attached example.

 

[Squid11]

Phil, I think the point of confusion is the structure of your data. The form function should be pretty straightforward after that is squared away.

If you have a Merchant-to-Customer relationship, as it seems, it would be very odd if that was a one-to-one. Usually one merchant could have various customers, and you would have a one-to-many relationship to model. If your data is truly one-to-one, as suggested above, then put it all into one table. You don't need to have two tables to make a form hide some of the data until a button is pushed...all of the data could be in one table, and it would simplify things.

I would suspect you have a one-to-many between merchants and customers, and after you have that set up with a relationship, you can use the form wizard, which will ask you if you want to use a link button....

good luck.

 

[PhilD]

Thanks for all your help so far

I'm not very good at explaining this I am !! Sorry

Neileg, Thats Exactly what I wanted, fantastic

Each record change in the main for brings up a different customer.

I've a better understanding I think, I need the primary key in Tbl_main (MerchantId) to be linked to a key in tbl_customers.

A few novice questions now!!

does this key have to be the same or doesnt it matter, I guess the relationship between the two is the reason why the records are linked?

In order to link two tabvles together i gues you use a relationship between the two and looking at what everone says a 1 to many relationship with the one side being on your main table?

Now, How do I add a cmd button so the customer details appear as a pop up form on the main form!!

Neileg, squid, thanks for your help so far, it's slowly sinking in !!

Phil

 

[neileg]

I've a better understanding I think, I need the primary key in Tbl_main (MerchantId) to be linked to a key in tbl_customers.

A few novice questions now!!

does this key have to be the same or doesnt it matter, I guess the relationship between the two is the reason why the records are linked?

In order to link two tabvles together i gues you use a relationship between the two and looking at what everone says a 1 to many relationship with the one side being on your main table?

You don't use a relationship, either there is one inherent in your business model or there isn't. If there is, then this should be refelcted in your database structure. If you look at the tables in my example you will see that the customer table holds the merchant ID in each record. This field is used to link the main form and the sub form together so the the data is synchronised.

 

[PhilD]

i think i'm condusing people with my table names !!

the Tbl_Customers holds the merchant names and address, so lets rename it Tbl_merchants.

I want Tbl_main to be the main form which will hold infomation about the merchant such as trading name, comapny number, turnover, comments. On this form I want cmd button called MERCHANT DETAILS which will bring up another form which will hold the merchants name and address and contact details when pressed.

Each record in Tbl_main will have a different customer details on it.

I'm guessing that as Neil just said I wont need a relationship between the two tables i can just use one table which is fine.

Is this the best way?? after that its only the form i need to learn how to do!!

I'm getting there.... albeit slowly!!!

Thanks for bearing with me so far!

Phil

 

[neileg]

i think i'm condusing people with my table names !!

the Tbl_Customers holds the merchant names and address, so lets rename it Tbl_merchants.

I want Tbl_main to be the main form which will hold infomation about the merchant such as trading name, comapny number, turnover, comments. On this form I want cmd button called MERCHANT DETAILS which will bring up another form which will hold the merchants name and address and contact details when pressed.

Each record in Tbl_main will have a different customer details on it.

I'm guessing that as Neil just said I wont need a relationship between the two tables i can just use one table which is fine.

Is this the best way?? after that its only the form i need to learn how to do!!

I'm getting there.... albeit slowly!!!

Thanks for bearing with me so far!

Phil

Nope, I'm more confused than ever! You are using the concept of tables and forms as if they are interchangeable, they aren't.

While we're renaming tables, let's drop the Tbl_Main. How about Tbl_Merchants and Tbl_Customers?

I think You have a set of data that is about customers that is related to another set of data that is about merchants. The link between the two will need to be established by holding the merchant number in the customer record.

Now the forms you want can take data from either table or both, but your description seems to suggest that it's all about merchants. Is that right?

Perhaps it would be easier if you mock up up the forms you want in Word or Excel or something and post it here.

 

[PhilD]

Right I have attached the file so everyone can see what i mean..

I am not bothered if i only need 2 tables or if one will do. More important is the Merchants contact details appearing in another form from a command button on the main form.

I'm thinking that one table might be best where all the contact details are already in the record.

 

[PhilD]

Help

any one got any ideas please.

 

[neileg]

OK, I'm still here, still trying to understand. I've looked at your file and I don't know what you want. The problem is that you have not yet explained who these parties are and how they are related and what the business process is all about. You need to do this in English, and explain it so that a dummy like me can understand. It may be perfectly obvious to you, but not to other people.

Here's some questions that need to be answered:
1) Who are the customers? Are they customers of the owner of the database or are they customers of the Merchant? In other words, who is selling to who?
2) The term merchant in the UK is usually used to identify a credit card company but in the US usually means an agent or supplier. Which is it?
3) Does every customer have one and only one merchant?
4) Do merchants relate to multiple customers?

Getting the popup form is going to be a doddle once we get the relationship sorted out!

 

[PhilD]

Hi Neil

Happy New Year....

I'll do my best to explain this as I'm confusing everyon, I'm the dummy here by not explaining it right.

I'm based in uk so everthing regarding terms etc are uk terms such as merchants.

The database is for keeping track of merchants (Ie retailers) who take credit card debit cards and switch payments at their stores.
Each merchant Will have just one unique merchant number. Even if they have several stores they will still have one merchant number.
(This merchant number equates to one record in the main table.)

The Merchant details is the name, address, contact number of the shop. Ie each shop will have different merchant contact details.

So not sure if I need 2 tables or just one?

Answer to 3) - so yes, each merchant has only one set of contact details.

4} Merchant relate to only 1 customer (Customer being Merchant contact details)

Basically i think that all i need is one table with ALL the merchant details in it, ie Merchant Number, Shop name and contact details all in one table?

Hope i've explained this better.

 

[Squid11]

This thread is like a chinese finger trap....lol

I'm trying to understand the relationship between merchants, shops, and customers, and I think it is still a pile of spaghetti.

Phil, I'm trying to understand your terms, but it's no luck. I think you may need to relabel things. In your post above you seem to be mixing the terms Merchant and Shops....Your plain language description still seems like it has circular references.

Would it help to call things:

Owners
Shops
Customers

??

As in one owner can own multiple shops and each shop can relate to multiple customers?

Your use of Merchant to describe someone who owns multiple shops and then to describe the details for the shops is frying my brain....lol