Need advice on database design

[k7i5t3n]

Hello,

I have a few major problems with my database that I'm trying to tackle.
I would like my main form A to contain customers, warranty issues (many
issues to one customer), and visit details (many visits to one issue).
I have separate tables for customers, warranty issues, and visit
details. I created a sub-subform C (visit details) within a subform B
(warranty issues) and put this subform B (with sub-subform C) inside a
tab control box on my main customer form A.

1. Functionality - I would like to be able to use the primary page to
search for a "Lot_Number & Neighborhood" (2 fields combined by
expression into 1) and if it's not already in the database, then I'd
like to add it (and then include information on the subforms as well.
Because the form is based on a table and not a query, the drop-down
will only show the field data associated with the single record. What's
the best way to set this up so that I can search for a "Lot_Number &
Neighborhood" and if it's not there, add a new customer record?

2. When I skip from one customer record to the next on the main form A,
it scrolls through the corresponding issues in my subform B and
sub-subform C properly, but since the sub-subform C executes prior to
the subform B, the linking fields between the two are different for a
split second and it throughs a parameter request.

It's entirely possible I didn't set up my links correctly or poorly
designed this database. Any suggestions as to how to achieve the
database I'm looking to create? Thanks!!

Kristen

 

[k7i5t3n]

Allen,

Thanks for your quick response! I'm beginning to play around with the
first issue. The code does work, but because it's unbound, when I use
it to create a new record, it doesn't autofill the other linked boxes
(from table "E") on the main form that are related to the Lot Number /
Neighborhood. Am I trying to have my cake and eat it too?

Also, I don't know how to remove the parameters from their source
queries... I don't realize how I added the parameter to begin with. I
have filled in the LinkMasterFields and LinkChildFields properties...
the parameter that keeps popping up is the primary key (autonumber)
field for the SubForm B. Any other suggestions on this one? Perhaps my
main form A and subform B shouldn't both have autonumbers? Thanks so
much!

Kristen

Hi Kristen. The structure you created sounds fine.

The subform and sub-subform should work without parameters. Try removing the
parameters from their source queries. Use the LinkMasterFields and
LinkChildFields properties of the subform controls instead. That should
solve the timing issue (#2.)

Re #1, you can use a query as the RowSource for your combo.
Something like this:
SELECT [ClientID],
[Lot_Number] & [Neighborhood] AS FullAddress,
ClientName
FROM tblClient
ORDER BY [Lot_Number], [Neighborhood], [ClientID];
Then set these properties for the combo:
Column Count 3
Column Widths 0; 2"; 1.8"
List Width 4"
The unbound combo will now display the lot number + neighborhood so you can
search on that, but you can match it to the (hidden) ClientID. If you're not
sure what code to put into the combo's AfterUpdate event procedure, see:
http://allenbrowne.com/ser-03.html

I have assumed here that the address is in the client table, not the issue
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a few major problems with my database that I'm trying to tackle.
I would like my main form A to contain customers, warranty issues (many
issues to one customer), and visit details (many visits to one issue).
I have separate tables for customers, warranty issues, and visit
details. I created a sub-subform C (visit details) within a subform B
(warranty issues) and put this subform B (with sub-subform C) inside a
tab control box on my main customer form A.

1. Functionality - I would like to be able to use the primary page to
search for a "Lot_Number & Neighborhood" (2 fields combined by
expression into 1) and if it's not already in the database, then I'd
like to add it (and then include information on the subforms as well.
Because the form is based on a table and not a query, the drop-down
will only show the field data associated with the single record. What's
the best way to set this up so that I can search for a "Lot_Number &
Neighborhood" and if it's not there, add a new customer record?

2. When I skip from one customer record to the next on the main form A,
it scrolls through the corresponding issues in my subform B and
sub-subform C properly, but since the sub-subform C executes prior to
the subform B, the linking fields between the two are different for a
split second and it throughs a parameter request.

It's entirely possible I didn't set up my links correctly or poorly
designed this database. Any suggestions as to how to achieve the
database I'm looking to create? Thanks!!

Kristen

 

[k7i5t3n]

Ok, i don't think the parameter isn't popping up b/c of the reason I
had previously thought. when i open the subform B/sub-subform C
directly, the parameter doesn't pop up until after I've scrolled
through all the records, but it will happen when go off the last record
and try to add a new one. It happens on the main form A when I scroll
from each customer to customer.

Allen,

Thanks for your quick response! I'm beginning to play around with the
first issue. The code does work, but because it's unbound, when I use
it to create a new record, it doesn't autofill the other linked boxes
(from table "E") on the main form that are related to the Lot Number /
Neighborhood. Am I trying to have my cake and eat it too?

Also, I don't know how to remove the parameters from their source
queries... I don't realize how I added the parameter to begin with. I
have filled in the LinkMasterFields and LinkChildFields properties...
the parameter that keeps popping up is the primary key (autonumber)
field for the SubForm B. Any other suggestions on this one? Perhaps my
main form A and subform B shouldn't both have autonumbers? Thanks so
much!

Kristen

Hi Kristen. The structure you created sounds fine.

The subform and sub-subform should work without parameters. Try removing the
parameters from their source queries. Use the LinkMasterFields and
LinkChildFields properties of the subform controls instead. That should
solve the timing issue (#2.)

Re #1, you can use a query as the RowSource for your combo.
Something like this:
SELECT [ClientID],
[Lot_Number] & [Neighborhood] AS FullAddress,
ClientName
FROM tblClient
ORDER BY [Lot_Number], [Neighborhood], [ClientID];
Then set these properties for the combo:
Column Count 3
Column Widths 0; 2"; 1.8"
List Width 4"
The unbound combo will now display the lot number + neighborhood so you can
search on that, but you can match it to the (hidden) ClientID. If you're not
sure what code to put into the combo's AfterUpdate event procedure, see:
http://allenbrowne.com/ser-03.html

I have assumed here that the address is in the client table, not the issue
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a few major problems with my database that I'm trying to tackle.
I would like my main form A to contain customers, warranty issues (many
issues to one customer), and visit details (many visits to one issue).
I have separate tables for customers, warranty issues, and visit
details. I created a sub-subform C (visit details) within a subform B
(warranty issues) and put this subform B (with sub-subform C) inside a
tab control box on my main customer form A.

1. Functionality - I would like to be able to use the primary page to
search for a "Lot_Number & Neighborhood" (2 fields combined by
expression into 1) and if it's not already in the database, then I'd
like to add it (and then include information on the subforms as well.
Because the form is based on a table and not a query, the drop-down
will only show the field data associated with the single record. What's
the best way to set this up so that I can search for a "Lot_Number &
Neighborhood" and if it's not there, add a new customer record?

2. When I skip from one customer record to the next on the main form A,
it scrolls through the corresponding issues in my subform B and
sub-subform C properly, but since the sub-subform C executes prior to
the subform B, the linking fields between the two are different for a
split second and it throughs a parameter request.

It's entirely possible I didn't set up my links correctly or poorly
designed this database. Any suggestions as to how to achieve the
database I'm looking to create? Thanks!!

Kristen

 

[Allen Browne]

Hi Kristen. The structure you created sounds fine.

The subform and sub-subform should work without parameters. Try removing the
parameters from their source queries. Use the LinkMasterFields and
LinkChildFields properties of the subform controls instead. That should
solve the timing issue (#2.)

Re #1, you can use a query as the RowSource for your combo.
Something like this:
SELECT [ClientID],
[Lot_Number] & [Neighborhood] AS FullAddress,
ClientName
FROM tblClient
ORDER BY [Lot_Number], [Neighborhood], [ClientID];
Then set these properties for the combo:
Column Count 3
Column Widths 0; 2"; 1.8"
List Width 4"
The unbound combo will now display the lot number + neighborhood so you can
search on that, but you can match it to the (hidden) ClientID. If you're not
sure what code to put into the combo's AfterUpdate event procedure, see:
http://allenbrowne.com/ser-03.html

I have assumed here that the address is in the client table, not the issue
table.

 

[k7i5t3n]

After more testing, it appears it's asking for the parameter if the
subform B or sub-subform C value is null. I should usually have a value
for subform B, but I won't always have a corresponding sub-subform C
value. And so obviously when I click to add a new record, it will pop
up, because there's no value... How do I get around this?

The unexpected parameter means there is something that Access can't identify
the name of.

The name requested in the dialog tells you what it's looking for.

It might be a misspelling of a field or table name, or it might be an
invalid name somewhere, e.g.: ControlSource of a field, Filter or OrderBy
property of Form, LinkMasterFields or LinkChildFields of a subform, an
expression, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok, i don't think the parameter isn't popping up b/c of the reason I
had previously thought. when i open the subform B/sub-subform C
directly, the parameter doesn't pop up until after I've scrolled
through all the records, but it will happen when go off the last record
and try to add a new one. It happens on the main form A when I scroll
from each customer to customer.

Allen,

Thanks for your quick response! I'm beginning to play around with the
first issue. The code does work, but because it's unbound, when I use
it to create a new record, it doesn't autofill the other linked boxes
(from table "E") on the main form that are related to the Lot Number /
Neighborhood. Am I trying to have my cake and eat it too?

Also, I don't know how to remove the parameters from their source
queries... I don't realize how I added the parameter to begin with. I
have filled in the LinkMasterFields and LinkChildFields properties...
the parameter that keeps popping up is the primary key (autonumber)
field for the SubForm B. Any other suggestions on this one? Perhaps my
main form A and subform B shouldn't both have autonumbers? Thanks so
much!

Kristen

Allen Browne wrote:
Hi Kristen. The structure you created sounds fine.

The subform and sub-subform should work without parameters. Try
removing the
parameters from their source queries. Use the LinkMasterFields and
LinkChildFields properties of the subform controls instead. That should
solve the timing issue (#2.)

Re #1, you can use a query as the RowSource for your combo.
Something like this:
SELECT [ClientID],
[Lot_Number] & [Neighborhood] AS FullAddress,
ClientName
FROM tblClient
ORDER BY [Lot_Number], [Neighborhood], [ClientID];
Then set these properties for the combo:
Column Count 3
Column Widths 0; 2"; 1.8"
List Width 4"
The unbound combo will now display the lot number + neighborhood so you
can
search on that, but you can match it to the (hidden) ClientID. If
you're not
sure what code to put into the combo's AfterUpdate event procedure,
see:
http://allenbrowne.com/ser-03.html

I have assumed here that the address is in the client table, not the
issue
table.

Hello,

I have a few major problems with my database that I'm trying to
tackle.
I would like my main form A to contain customers, warranty issues
(many
issues to one customer), and visit details (many visits to one
issue).
I have separate tables for customers, warranty issues, and visit
details. I created a sub-subform C (visit details) within a subform B
(warranty issues) and put this subform B (with sub-subform C) inside
a
tab control box on my main customer form A.

1. Functionality - I would like to be able to use the primary page to
search for a "Lot_Number & Neighborhood" (2 fields combined by
expression into 1) and if it's not already in the database, then I'd
like to add it (and then include information on the subforms as well.
Because the form is based on a table and not a query, the drop-down
will only show the field data associated with the single record.
What's
the best way to set this up so that I can search for a "Lot_Number &
Neighborhood" and if it's not there, add a new customer record?

2. When I skip from one customer record to the next on the main form
A,
it scrolls through the corresponding issues in my subform B and
sub-subform C properly, but since the sub-subform C executes prior to
the subform B, the linking fields between the two are different for a
split second and it throughs a parameter request.

It's entirely possible I didn't set up my links correctly or poorly
designed this database. Any suggestions as to how to achieve the
database I'm looking to create? Thanks!!

Kristen

 

[Allen Browne]

The unexpected parameter means there is something that Access can't identify
the name of.

The name requested in the dialog tells you what it's looking for.

It might be a misspelling of a field or table name, or it might be an
invalid name somewhere, e.g.: ControlSource of a field, Filter or OrderBy
property of Form, LinkMasterFields or LinkChildFields of a subform, an
expression, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok, i don't think the parameter isn't popping up b/c of the reason I
had previously thought. when i open the subform B/sub-subform C
directly, the parameter doesn't pop up until after I've scrolled
through all the records, but it will happen when go off the last record
and try to add a new one. It happens on the main form A when I scroll
from each customer to customer.

Allen,

Thanks for your quick response! I'm beginning to play around with the
first issue. The code does work, but because it's unbound, when I use
it to create a new record, it doesn't autofill the other linked boxes
(from table "E") on the main form that are related to the Lot Number /
Neighborhood. Am I trying to have my cake and eat it too?

Also, I don't know how to remove the parameters from their source
queries... I don't realize how I added the parameter to begin with. I
have filled in the LinkMasterFields and LinkChildFields properties...
the parameter that keeps popping up is the primary key (autonumber)
field for the SubForm B. Any other suggestions on this one? Perhaps my
main form A and subform B shouldn't both have autonumbers? Thanks so
much!

Kristen

Hi Kristen. The structure you created sounds fine.

The subform and sub-subform should work without parameters. Try
removing the
parameters from their source queries. Use the LinkMasterFields and
LinkChildFields properties of the subform controls instead. That should
solve the timing issue (#2.)

Re #1, you can use a query as the RowSource for your combo.
Something like this:
SELECT [ClientID],
[Lot_Number] & [Neighborhood] AS FullAddress,
ClientName
FROM tblClient
ORDER BY [Lot_Number], [Neighborhood], [ClientID];
Then set these properties for the combo:
Column Count 3
Column Widths 0; 2"; 1.8"
List Width 4"
The unbound combo will now display the lot number + neighborhood so you
can
search on that, but you can match it to the (hidden) ClientID. If
you're not
sure what code to put into the combo's AfterUpdate event procedure,
see:
http://allenbrowne.com/ser-03.html

I have assumed here that the address is in the client table, not the
issue
table.

Hello,

I have a few major problems with my database that I'm trying to
tackle.
I would like my main form A to contain customers, warranty issues
(many
issues to one customer), and visit details (many visits to one
issue).
I have separate tables for customers, warranty issues, and visit
details. I created a sub-subform C (visit details) within a subform B
(warranty issues) and put this subform B (with sub-subform C) inside
a
tab control box on my main customer form A.

1. Functionality - I would like to be able to use the primary page to
search for a "Lot_Number & Neighborhood" (2 fields combined by
expression into 1) and if it's not already in the database, then I'd
like to add it (and then include information on the subforms as well.
Because the form is based on a table and not a query, the drop-down
will only show the field data associated with the single record.
What's
the best way to set this up so that I can search for a "Lot_Number &
Neighborhood" and if it's not there, add a new customer record?

2. When I skip from one customer record to the next on the main form
A,
it scrolls through the corresponding issues in my subform B and
sub-subform C properly, but since the sub-subform C executes prior to
the subform B, the linking fields between the two are different for a
split second and it throughs a parameter request.

It's entirely possible I didn't set up my links correctly or poorly
designed this database. Any suggestions as to how to achieve the
database I'm looking to create? Thanks!!

Kristen

 

[Allen Browne]

Cancel the insert if the parent form is at a new record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a record in the parent form first."
End If
End Sub

If that doesn't solve the problem, there's something else going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

After more testing, it appears it's asking for the parameter if the
subform B or sub-subform C value is null. I should usually have a value
for subform B, but I won't always have a corresponding sub-subform C
value. And so obviously when I click to add a new record, it will pop
up, because there's no value... How do I get around this?

The unexpected parameter means there is something that Access can't
identify
the name of.

The name requested in the dialog tells you what it's looking for.

It might be a misspelling of a field or table name, or it might be an
invalid name somewhere, e.g.: ControlSource of a field, Filter or OrderBy
property of Form, LinkMasterFields or LinkChildFields of a subform, an
expression, ...

Ok, i don't think the parameter isn't popping up b/c of the reason I
had previously thought. when i open the subform B/sub-subform C
directly, the parameter doesn't pop up until after I've scrolled
through all the records, but it will happen when go off the last record
and try to add a new one. It happens on the main form A when I scroll
from each customer to customer.

k7i5t3n wrote:
Allen,

Thanks for your quick response! I'm beginning to play around with the
first issue. The code does work, but because it's unbound, when I use
it to create a new record, it doesn't autofill the other linked boxes
(from table "E") on the main form that are related to the Lot Number /
Neighborhood. Am I trying to have my cake and eat it too?

Also, I don't know how to remove the parameters from their source
queries... I don't realize how I added the parameter to begin with. I
have filled in the LinkMasterFields and LinkChildFields properties...
the parameter that keeps popping up is the primary key (autonumber)
field for the SubForm B. Any other suggestions on this one? Perhaps my
main form A and subform B shouldn't both have autonumbers? Thanks so
much!

Kristen

Allen Browne wrote:
Hi Kristen. The structure you created sounds fine.

The subform and sub-subform should work without parameters. Try
removing the
parameters from their source queries. Use the LinkMasterFields and
LinkChildFields properties of the subform controls instead. That
should
solve the timing issue (#2.)

Re #1, you can use a query as the RowSource for your combo.
Something like this:
SELECT [ClientID],
[Lot_Number] & [Neighborhood] AS FullAddress,
ClientName
FROM tblClient
ORDER BY [Lot_Number], [Neighborhood], [ClientID];
Then set these properties for the combo:
Column Count 3
Column Widths 0; 2"; 1.8"
List Width 4"
The unbound combo will now display the lot number + neighborhood so
you
can
search on that, but you can match it to the (hidden) ClientID. If
you're not
sure what code to put into the combo's AfterUpdate event procedure,
see:
http://allenbrowne.com/ser-03.html

I have assumed here that the address is in the client table, not the
issue
table.

Hello,

I have a few major problems with my database that I'm trying to
tackle.
I would like my main form A to contain customers, warranty issues
(many
issues to one customer), and visit details (many visits to one
issue).
I have separate tables for customers, warranty issues, and visit
details. I created a sub-subform C (visit details) within a
subform B
(warranty issues) and put this subform B (with sub-subform C)
inside
a
tab control box on my main customer form A.

1. Functionality - I would like to be able to use the primary page
to
search for a "Lot_Number & Neighborhood" (2 fields combined by
expression into 1) and if it's not already in the database, then
I'd
like to add it (and then include information on the subforms as
well.
Because the form is based on a table and not a query, the
drop-down
will only show the field data associated with the single record.
What's
the best way to set this up so that I can search for a "Lot_Number
&
Neighborhood" and if it's not there, add a new customer record?

2. When I skip from one customer record to the next on the main
form
A,
it scrolls through the corresponding issues in my subform B and
sub-subform C properly, but since the sub-subform C executes prior
to
the subform B, the linking fields between the two are different
for a
split second and it throughs a parameter request.

It's entirely possible I didn't set up my links correctly or
poorly
designed this database. Any suggestions as to how to achieve the
database I'm looking to create? Thanks!!

Kristen

 

[k7i5t3n]

Allen,

Thanks for all your time! Unfortunately, it hasn't solved the
problem... I guess I'll just keep messing around with it. Thanks
anyways!

Cancel the insert if the parent form is at a new record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a record in the parent form first."
End If
End Sub

If that doesn't solve the problem, there's something else going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

After more testing, it appears it's asking for the parameter if the
subform B or sub-subform C value is null. I should usually have a value
for subform B, but I won't always have a corresponding sub-subform C
value. And so obviously when I click to add a new record, it will pop
up, because there's no value... How do I get around this?

The unexpected parameter means there is something that Access can't
identify
the name of.

The name requested in the dialog tells you what it's looking for.

It might be a misspelling of a field or table name, or it might be an
invalid name somewhere, e.g.: ControlSource of a field, Filter or OrderBy
property of Form, LinkMasterFields or LinkChildFields of a subform, an
expression, ...

Ok, i don't think the parameter isn't popping up b/c of the reason I
had previously thought. when i open the subform B/sub-subform C
directly, the parameter doesn't pop up until after I've scrolled
through all the records, but it will happen when go off the last record
and try to add a new one. It happens on the main form A when I scroll
from each customer to customer.

k7i5t3n wrote:
Allen,

Thanks for your quick response! I'm beginning to play around with the
first issue. The code does work, but because it's unbound, when I use
it to create a new record, it doesn't autofill the other linked boxes
(from table "E") on the main form that are related to the Lot Number /
Neighborhood. Am I trying to have my cake and eat it too?

Also, I don't know how to remove the parameters from their source
queries... I don't realize how I added the parameter to begin with. I
have filled in the LinkMasterFields and LinkChildFields properties...
the parameter that keeps popping up is the primary key (autonumber)
field for the SubForm B. Any other suggestions on this one? Perhaps my
main form A and subform B shouldn't both have autonumbers? Thanks so
much!

Kristen

Allen Browne wrote:
Hi Kristen. The structure you created sounds fine.

The subform and sub-subform should work without parameters. Try
removing the
parameters from their source queries. Use the LinkMasterFields and
LinkChildFields properties of the subform controls instead. That
should
solve the timing issue (#2.)

Re #1, you can use a query as the RowSource for your combo.
Something like this:
SELECT [ClientID],
[Lot_Number] & [Neighborhood] AS FullAddress,
ClientName
FROM tblClient
ORDER BY [Lot_Number], [Neighborhood], [ClientID];
Then set these properties for the combo:
Column Count 3
Column Widths 0; 2"; 1.8"
List Width 4"
The unbound combo will now display the lot number + neighborhood so
you
can
search on that, but you can match it to the (hidden) ClientID. If
you're not
sure what code to put into the combo's AfterUpdate event procedure,
see:
http://allenbrowne.com/ser-03.html

I have assumed here that the address is in the client table, not the
issue
table.

Hello,

I have a few major problems with my database that I'm trying to
tackle.
I would like my main form A to contain customers, warranty issues
(many
issues to one customer), and visit details (many visits to one
issue).
I have separate tables for customers, warranty issues, and visit
details. I created a sub-subform C (visit details) within a
subform B
(warranty issues) and put this subform B (with sub-subform C)
inside
a
tab control box on my main customer form A.

1. Functionality - I would like to be able to use the primary page
to
search for a "Lot_Number & Neighborhood" (2 fields combined by
expression into 1) and if it's not already in the database, then
I'd
like to add it (and then include information on the subforms as
well.
Because the form is based on a table and not a query, the
drop-down
will only show the field data associated with the single record.
What's
the best way to set this up so that I can search for a "Lot_Number
&
Neighborhood" and if it's not there, add a new customer record?

2. When I skip from one customer record to the next on the main
form
A,
it scrolls through the corresponding issues in my subform B and
sub-subform C properly, but since the sub-subform C executes prior
to
the subform B, the linking fields between the two are different
for a
split second and it throughs a parameter request.

It's entirely possible I didn't set up my links correctly or
poorly
designed this database. Any suggestions as to how to achieve the
database I'm looking to create? Thanks!!

Kristen