Need to add credit accounts to members record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When i have my members record open using the form, I want to be able to enter
his credit accounts and then submit and then they would show up below and be
able to updated if necessary. I tried doing this creating a subform, but it
basically saves the accounts in a table of all my members accounts combined.
I do not want that. I want it to show up when i view a the members record?
Any advice? I know it can be done since the company I used to work for had
it like this in access.
 
When i have my members record open using the form, I want to be able to enter
his credit accounts and then submit and then they would show up below and be
able to updated if necessary. I tried doing this creating a subform, but it
basically saves the accounts in a table of all my members accounts combined.
I do not want that. I want it to show up when i view a the members record?
Any advice? I know it can be done since the company I used to work for had
it like this in access.
Click to expand...

What tables do you have? How are they related?

What I'd suggest (if you don't have it that way now) is to have a
MemberID field in the Accounts table, to identify whose account each
record is. You can then set the Master and Child Link Fields of the
subform to MemberID; the subform will then show only the accounts for
the individual member, not for all members.

John W. Vinson[MVP]
 
John Vinson said:
What tables do you have? How are they related?

What I'd suggest (if you don't have it that way now) is to have a
MemberID field in the Accounts table, to identify whose account each
record is. You can then set the Master and Child Link Fields of the
subform to MemberID; the subform will then show only the accounts for
the individual member, not for all members.

John W. Vinson[MVP]
Click to expand...



Yes I have a members table with the member id and all the members fields,
etc..Then I had a negative accounts table with the members id and all the
fields, then on the form, I have the members form, and a subform for the
accounts. But when I type in a account, it dissappears. I want to be able to
type in the information and hit submit or enter, and it will drop down to a
list below (spreadsheet type of thing i guess. Just like enetering the info
in the subform and submitting it to a list below so whenever i go to this
member in the form, It will show all of his accounts listed.
 
Yes I have a members table with the member id and all the members fields,
etc..Then I had a negative accounts table with the members id and all the
fields, then on the form, I have the members form, and a subform for the
accounts. But when I type in a account, it dissappears. I want to be able to
type in the information and hit submit or enter, and it will drop down to a
list below (spreadsheet type of thing i guess. Just like enetering the info
in the subform and submitting it to a list below so whenever i go to this
member in the form, It will show all of his accounts listed.
Click to expand...

I'm sorry, but this has me totally baffled. What's a "negative"
accounts table? What is the Recordsource property for this subform?
What is its Master and Child Link Field? Is it a Datasheet form,
single form, or continuous form? What "disappears"?

And I have NO clue what you might mean by "a list below".

Typically one would use a single Form for the mainform, and a
Continuous Form (view the form's properties and look at the Default
View property) as a subform. The subform would be based on the
Accounts table, or a query sorting the Accounts table
(chronologically, sorting by the date field, I'd guess).

The data on the subform should show a list of all that member's
accounts. Nothing should "disappear" and there would be no need for a
separeate "list".

John W. Vinson[MVP]
 
John Vinson said:
I'm sorry, but this has me totally baffled. What's a "negative"
accounts table? What is the Recordsource property for this subform?
What is its Master and Child Link Field? Is it a Datasheet form,
single form, or continuous form? What "disappears"?

And I have NO clue what you might mean by "a list below".

Typically one would use a single Form for the mainform, and a
Continuous Form (view the form's properties and look at the Default
View property) as a subform. The subform would be based on the
Accounts table, or a query sorting the Accounts table
(chronologically, sorting by the date field, I'd guess).

The data on the subform should show a list of all that member's
accounts. Nothing should "disappear" and there would be no need for a
separeate "list".

John W. Vinson[MVP]
Click to expand...


Sorry for the confusion...Negative accounts are bascially my members credit
bureau accounts that are reporting negative information.

What I mean by a list below is...I want to be able to enter their
information in the fields, and when im done, it will drop the info down to a
spreadsheet type of form below into a list. Then repeat , and so on..Until
Im done entering info. I tried doing all this already and it is not workin
right. I started a form for Members and one for negative accounts. I then
inserted the negative accounts subform. The child and master link is
MembersID on both forms. But when enter the info, the members id stays on
auto number and does not line up with the Member form record..Helppppp...
 
What I mean by a list below is...I want to be able to enter their
information in the fields, and when im done, it will drop the info down to a
spreadsheet type of form below into a list. Then repeat , and so on..Until
Im done entering info. I tried doing all this already and it is not workin
right. I started a form for Members and one for negative accounts. I then
inserted the negative accounts subform. The child and master link is
MembersID on both forms. But when enter the info, the members id stays on
auto number and does not line up with the Member form record..Helppppp...
Click to expand...

The MemberID in the Accounts table MUST NOT be an Autonumber. It
should be a Long Integer. Change the table design so that this is a
Long Integer number instead.

An Autonumber has one purpose ONLY - to provide a unique primary key.
As such it cannot be controlled - there will always be gaps, for one
thing; and it's totally inappropriate as a foreign key (since it
*CAN'T* be controlled, and even if it could it would allow you one and
only one negative account per customer, since an autonumber is
unique).

And don't think about "dropping down into a spreadsheet type of form".
Simply use a Continuous Form, or a Datasheet Form, *as* the subform,
and enter the data into it directly (on the bottom line). Then it's
right there - no need to transfer it.

John W. Vinson[MVP]
 
John Vinson said:
The MemberID in the Accounts table MUST NOT be an Autonumber. It
should be a Long Integer. Change the table design so that this is a
Long Integer number instead.

An Autonumber has one purpose ONLY - to provide a unique primary key.
As such it cannot be controlled - there will always be gaps, for one
thing; and it's totally inappropriate as a foreign key (since it
*CAN'T* be controlled, and even if it could it would allow you one and
only one negative account per customer, since an autonumber is
unique).

And don't think about "dropping down into a spreadsheet type of form".
Simply use a Continuous Form, or a Datasheet Form, *as* the subform,
and enter the data into it directly (on the bottom line). Then it's
right there - no need to transfer it.

John W. Vinson[MVP]
Click to expand...


Ok well I tried that. Not working right. Let me make sure im doing this
right. I will list everything im doing below step by step

1. I have my main table set up for my members with the members ID field. It
isnt autonumber, but a integer of 4 characters max. I do not provide a id,
When someone signs up from my services ONLINE OR I ENTER THEM MANUALLY, a 4
digit members ID is generated. The rest of the fields are for their personal
info, etc..

I created a 2nd table for Accounts. I made a membersID field as well for
that one. Both have that as a primary key. Should i have a different key name
rather than membersID as well? I then created a form with the Members table.
Below it, I inserted a subform, and when it asked me what Table/Query/Form I
wanted to use, I selected the accounst form with all the fields including
membersID.

Am I doing this right? When I was done, I saw the form below, and tested it.
I entered accounts in, but when I hit the arrow to view next client, and came
back , the accounts were gone and it also showed all the accounts i just
typed in for the client on the next clients as well. So i Dont know what I am
doing wrong. Can you tell me exactly what needs to be done after inserting
the subform, etc..

I really need to get this working, to make my life alot easier and able to
keep all my clients info in one place so eventually this can be put on their
login page on my site..

Thanks

Marisa
 
Ok well I tried that. Not working right. Let me make sure im doing this
right. I will list everything im doing below step by step

1. I have my main table set up for my members with the members ID field. It
isnt autonumber, but a integer of 4 characters max. I do not provide a id,
When someone signs up from my services ONLINE OR I ENTER THEM MANUALLY, a 4
digit members ID is generated. The rest of the fields are for their personal
info, etc..
Click to expand...

An Integer doesn't have "characters" - it's a 32 bit binary number. Do
you mean that you have the format set to only display four digits? If
so that's a display-only property.
I created a 2nd table for Accounts. I made a membersID field as well for
that one. Both have that as a primary key.
Click to expand...

A Primary Key is - *by definition* - unique within the table. There
can be one and only one record with a given value of the primary key.
If you make the MembersID the primary key of both tables, you are
saying that each Member can have one, and only one, account. I don't
think you want that!

Instead, use an AccountID - autonumber if you like - as the primary
key of the Accounts table, and a *separate* MembersID field of the
same datatype as the Members MemberID field as the foreign key.
Should i have a different key name
rather than membersID as well?
Click to expand...

The name of the field plays no part whatsoever in the relationship.
It's customary and convenient to use the same name for a "foreign key"
as the name of the primary key (i.e. call it MembersID) but it is not
essential to do so.
I then created a form with the Members table.
Below it, I inserted a subform, and when it asked me what Table/Query/Form I
wanted to use, I selected the accounst form with all the fields including
membersID.
Click to expand...

Well... the subform is *on* - inside - the mainform, not "below" it.
I'm not certain what you're saying here.

Am I doing this right? When I was done, I saw the form below, and tested it.
I entered accounts in, but when I hit the arrow to view next client, and came
back , the accounts were gone and it also showed all the accounts i just
typed in for the client on the next clients as well. So i Dont know what I am
doing wrong. Can you tell me exactly what needs to be done after inserting
the subform, etc..
Click to expand...

You may want to repost in a new thread, specifying that you're using
an ASP page. I've done all my work in Access MDB forms and I cannot
remember how (or even if you can!!) set up a Form/Subform on an ASP
page.

But, in an Access form, the linking takes place automatically. I
suspect that it's different on ASP. Sorry if I led you down the wrong
path, if that's the case!

John W. Vinson[MVP]
 
Back
Top