Can't figure this one out

[Dos Equis]

Hi all,

I am trying to make a form which will look up a record in one table and
allow me to input a child record in a second table. Basically I have a
subscribers database and want to be able to track payments.

I've tried to do this with a query to base the form on but get no
results once I attach the payment table to the query. I assume I'll
need to create a form and a subform. Would someone please point me in
the right direction?

Thanks,

Byron

 

[kingston via AccessMonster.com]

Create a form for table1 with the ability to look up a record. Create a form
for table2. Make sure both forms work the way you want. Open the form for
table1 in design mode and drag and drop the form for table2 into it. Access
will ask for the fields that you want to link the two together. That's it.
The form for table1 will automatically update the subform (you can add
records corresponding to the ID in the main form).

 

[Lynn Trapp]

Byron,
You'll need to tell us something about your database structure.

 

[Dos Equis]

Worked like a champ. Thank you,

Byron

Create a form for table1 with the ability to look up a record. Create a form
for table2. Make sure both forms work the way you want. Open the form for
table1 in design mode and drag and drop the form for table2 into it. Access
will ask for the fields that you want to link the two together. That's it.
The form for table1 will automatically update the subform (you can add
records corresponding to the ID in the main form).

 

[Dos Equis]

Looks like I spoke too soon. I am having a problem with this form.

It worked as a subscriber data edit form. It worked when I had just
the Payment options added. I've also added a subform for complaints
and now I get an error with the payments. The error is as follows:

The LinkMasterFields property setting has produced this error:
'Ambiguous name detected: btn_EditAnother_Click'

It comes up again once I hit ok. I assume this has to do with a second
subform being added?

Thanks for any help.

Byron

 

[John Vinson]

The LinkMasterFields property setting has produced this error:
'Ambiguous name detected: btn_EditAnother_Click'

It comes up again once I hit ok. I assume this has to do with a second
subform being added?

Open the Form's VBA code. There are evidently TWO Sub routines named
btn_EditAnother_Click, perhaps because you tried to create the button
a second time after deleting it.

Remove the old

Sub btn_EditAnother_Click()
<a bunch of code>
End Sub

It will typically be at the very top of the form's Module.

John W. Vinson[MVP]

 

[Dos Equis]

Fixed it. Thank you.

Byron

Open the Form's VBA code. There are evidently TWO Sub routines named
btn_EditAnother_Click, perhaps because you tried to create the button
a second time after deleting it.

Remove the old

Sub btn_EditAnother_Click()
<a bunch of code>
End Sub

It will typically be at the very top of the form's Module.

John W. Vinson[MVP]

 

[kingston via AccessMonster.com]

Do you have a nested subform or just two subforms in one main form? The
error seems to indicate that you've selected a button rather than a field to
link the main form and a subform? Check the Link Child/Master Fields
properties. Make sure all forms work independently.

Looks like I spoke too soon. I am having a problem with this form.

It worked as a subscriber data edit form. It worked when I had just
the Payment options added. I've also added a subform for complaints
and now I get an error with the payments. The error is as follows:

The LinkMasterFields property setting has produced this error:
'Ambiguous name detected: btn_EditAnother_Click'

It comes up again once I hit ok. I assume this has to do with a second
subform being added?

Thanks for any help.

Byron

Worked like a champ. Thank you,

[quoted text clipped - 25 lines]

 

[Dos Equis]

I followed your advice earlier. I opened the main form
(Subscriber/Company Data Edit Form) and dragged both sub forms onto it.
I then went to the properties field of both sub forms and used the
childlink to link the SubID on both subforms to the SubID on the master
form. This worked and went fine until I started trying to make a
button which would allow me to edit another record after I finished the
one I was on.

Would it help to have them nested? If so, what do I need to do? I'm
willing to try just about anything to improve this thing, I'd send it
to you if you wanted to see it...

I've been playing with access for several years, but never needed
anything this indepth. Now I'm learning lots and getting better, but
still don't know enough to ask the right questions. I have a couple of
books that I think have some of the information I need, but don't know
how to find it in them... not really ready to sit down and read 1200
pages just to find that one nugget that helps right now, and I do look
through them before I post just to see if it's an obvious answer.

Thanks for all your help,

Byron

Do you have a nested subform or just two subforms in one main form? The
error seems to indicate that you've selected a button rather than a field to
link the main form and a subform? Check the Link Child/Master Fields
properties. Make sure all forms work independently.

Looks like I spoke too soon. I am having a problem with this form.

It worked as a subscriber data edit form. It worked when I had just
the Payment options added. I've also added a subform for complaints
and now I get an error with the payments. The error is as follows:

The LinkMasterFields property setting has produced this error:
'Ambiguous name detected: btn_EditAnother_Click'

It comes up again once I hit ok. I assume this has to do with a second
subform being added?

Thanks for any help.

Byron

Worked like a champ. Thank you,

[quoted text clipped - 25 lines]

 

[kingston via AccessMonster.com]

I saw Jon Vinson's answer only after I posted my last response and I thought
it correctly diagnosed the problem. I guess I'm not sure what you're trying
to solve now and how I can help. Is your set-up working now?

Nesting subforms just means that there is a subform within a subform within a
main form. This is used where there are three levels of data; I wasn't sure
whether this was what you meant when you said you added another subform.
However, if both subforms are directly related to the main form, there is no
need to use nested subforms.

As far as adding a button to move to a new record, where are you trying to do
this? Let's use a convention like this: you have 3 forms - FormA, FormB,
FormC; if FormB is a subform of FormA, call the combination FormA(B); if
FormB and FormC are subforms of FormA, call the combination FormA(BC); if
Form C is a subform of FormB and this combination is a subform of Form A,
call this FormA(B(C)). Creating a button to move to a new record within the
same form should be fairly straightforward. In fact, you should be able to
use the Access button wizard to do this. Are you trying to move to a new
record in a subform? In general, I try not to duplicate built-in functions;
in this case, the built-in record navigation buttons allow users to move to a
new record. I feel that users should know how to perform rudimentary tasks
and have a basic understanding of Access (just like Excel or Word).
Otherwise, it's just too much hand-holding and unnecessary programming.

I followed your advice earlier. I opened the main form
(Subscriber/Company Data Edit Form) and dragged both sub forms onto it.
I then went to the properties field of both sub forms and used the
childlink to link the SubID on both subforms to the SubID on the master
form. This worked and went fine until I started trying to make a
button which would allow me to edit another record after I finished the
one I was on.

Would it help to have them nested? If so, what do I need to do? I'm
willing to try just about anything to improve this thing, I'd send it
to you if you wanted to see it...

I've been playing with access for several years, but never needed
anything this indepth. Now I'm learning lots and getting better, but
still don't know enough to ask the right questions. I have a couple of
books that I think have some of the information I need, but don't know
how to find it in them... not really ready to sit down and read 1200
pages just to find that one nugget that helps right now, and I do look
through them before I post just to see if it's an obvious answer.

Thanks for all your help,

Byron

Do you have a nested subform or just two subforms in one main form? The
error seems to indicate that you've selected a button rather than a field to

[quoted text clipped - 26 lines]

 

[Dos Equis]

Ok, the confusion is that I didn't provide enough info on structure.
Here's what I've got...

7 tables, bunch of queries, several forms, plans for reports...

The form I'm using references a query which serches the subscriber
table and returns only those record showing the same last name via a
pop-up dialog box in the query [Enter subscribers last name]. Might be
1, might be 7. I can edit all of those records with standard
navigation buttons.

The 2 sub forms are payments and complaints and follow the A(BC)
structure as both are sub forms of the first. The button I am trying
to create will in essence allow me to type in a new last name and
continue editing without having to shut down the form.

I know little about access and even less about coding so I am trying to
keep things simple. If I need to code, I'll learn how but I've been
working on this database for 6 months and would like to see it become
useful in the next 4 weeks. For experinced users, this may be
laughable but for me, it's a challenge I'd like to complete; and I'll
take all the help I can get so that I don't create something that can't
grow to cover gaps I didn't konw I had.

Thanks again for all your help, sharing your knowledge and experience
means alot to those of us who are rather cluless.

Byron

I saw Jon Vinson's answer only after I posted my last response and I thought
it correctly diagnosed the problem. I guess I'm not sure what you're trying
to solve now and how I can help. Is your set-up working now?

Nesting subforms just means that there is a subform within a subform within a
main form. This is used where there are three levels of data; I wasn't sure
whether this was what you meant when you said you added another subform.
However, if both subforms are directly related to the main form, there is no
need to use nested subforms.

As far as adding a button to move to a new record, where are you trying to do
this? Let's use a convention like this: you have 3 forms - FormA, FormB,
FormC; if FormB is a subform of FormA, call the combination FormA(B); if
FormB and FormC are subforms of FormA, call the combination FormA(BC); if
Form C is a subform of FormB and this combination is a subform of Form A,
call this FormA(B(C)). Creating a button to move to a new record within the
same form should be fairly straightforward. In fact, you should be able to
use the Access button wizard to do this. Are you trying to move to a new
record in a subform? In general, I try not to duplicate built-in functions;
in this case, the built-in record navigation buttons allow users to move to a
new record. I feel that users should know how to perform rudimentary tasks
and have a basic understanding of Access (just like Excel or Word).
Otherwise, it's just too much hand-holding and unnecessary programming.

I followed your advice earlier. I opened the main form
(Subscriber/Company Data Edit Form) and dragged both sub forms onto it.
I then went to the properties field of both sub forms and used the
childlink to link the SubID on both subforms to the SubID on the master
form. This worked and went fine until I started trying to make a
button which would allow me to edit another record after I finished the
one I was on.

Would it help to have them nested? If so, what do I need to do? I'm
willing to try just about anything to improve this thing, I'd send it
to you if you wanted to see it...

I've been playing with access for several years, but never needed
anything this indepth. Now I'm learning lots and getting better, but
still don't know enough to ask the right questions. I have a couple of
books that I think have some of the information I need, but don't know
how to find it in them... not really ready to sit down and read 1200
pages just to find that one nugget that helps right now, and I do look
through them before I post just to see if it's an obvious answer.

Thanks for all your help,

Byron

Do you have a nested subform or just two subforms in one main form? The
error seems to indicate that you've selected a button rather than a field to

[quoted text clipped - 26 lines]

 

[kingston via AccessMonster.com]

See if this will work for you:

Start with three simple forms - one for each table (no connection amongst
them whatsoever and not based on queries).

Embed FormB and FormC into FormA with the proper links - FormA(BC).

In design mode for FormA, view the form header and add a textbox (or combobox
if you'd like) to the header. It will probably be named Text# automatically;
let's say it is Text10. Add a button to the header and use the following
code for its OnClick event: Me.Requery

Create a query based on the table for FormA. Output all fields. For the
criteria for [Last Name], enter [Forms]![FormA]![Text10] (or for an inexact
search, use Like "*" & [Forms]![FormA]![Text10] & "*"). We'll call this
qryFormA.

Now in design mode for FormA, change the Record Source property from TableA
to qryFormA.

You should be done. The user will be able to enter a last name and click on
the button to retrieve the pertinent records. If the user wants to enter a
new record, he simply uses the navigation button for a new record. There are
a lot of ways this can be done, but this was the simplest one that I thought
I could explain decently.

Ok, the confusion is that I didn't provide enough info on structure.
Here's what I've got...

7 tables, bunch of queries, several forms, plans for reports...

The form I'm using references a query which serches the subscriber
table and returns only those record showing the same last name via a
pop-up dialog box in the query [Enter subscribers last name]. Might be
1, might be 7. I can edit all of those records with standard
navigation buttons.

The 2 sub forms are payments and complaints and follow the A(BC)
structure as both are sub forms of the first. The button I am trying
to create will in essence allow me to type in a new last name and
continue editing without having to shut down the form.

I know little about access and even less about coding so I am trying to
keep things simple. If I need to code, I'll learn how but I've been
working on this database for 6 months and would like to see it become
useful in the next 4 weeks. For experinced users, this may be
laughable but for me, it's a challenge I'd like to complete; and I'll
take all the help I can get so that I don't create something that can't
grow to cover gaps I didn't konw I had.

Thanks again for all your help, sharing your knowledge and experience
means alot to those of us who are rather cluless.

Byron

I saw Jon Vinson's answer only after I posted my last response and I thought
it correctly diagnosed the problem. I guess I'm not sure what you're trying

[quoted text clipped - 53 lines]

 

[Dos Equis]

Kingston,

I did everything you said and once it was complete I entered a name and
hit the button. An error was returned stating that the macro Me was
not available. I'm using access 2000, should this macro be available?

Byron

See if this will work for you:

Start with three simple forms - one for each table (no connection amongst
them whatsoever and not based on queries).

Embed FormB and FormC into FormA with the proper links - FormA(BC).

In design mode for FormA, view the form header and add a textbox (or combobox
if you'd like) to the header. It will probably be named Text# automatically;
let's say it is Text10. Add a button to the header and use the following
code for its OnClick event: Me.Requery

Create a query based on the table for FormA. Output all fields. For the
criteria for [Last Name], enter [Forms]![FormA]![Text10] (or for an inexact
search, use Like "*" & [Forms]![FormA]![Text10] & "*"). We'll call this
qryFormA.

Now in design mode for FormA, change the Record Source property from TableA
to qryFormA.

You should be done. The user will be able to enter a last name and click on
the button to retrieve the pertinent records. If the user wants to enter a
new record, he simply uses the navigation button for a new record. There are
a lot of ways this can be done, but this was the simplest one that I thought
I could explain decently.

Ok, the confusion is that I didn't provide enough info on structure.
Here's what I've got...

7 tables, bunch of queries, several forms, plans for reports...

The form I'm using references a query which serches the subscriber
table and returns only those record showing the same last name via a
pop-up dialog box in the query [Enter subscribers last name]. Might be
1, might be 7. I can edit all of those records with standard
navigation buttons.

The 2 sub forms are payments and complaints and follow the A(BC)
structure as both are sub forms of the first. The button I am trying
to create will in essence allow me to type in a new last name and
continue editing without having to shut down the form.

I know little about access and even less about coding so I am trying to
keep things simple. If I need to code, I'll learn how but I've been
working on this database for 6 months and would like to see it become
useful in the next 4 weeks. For experinced users, this may be
laughable but for me, it's a challenge I'd like to complete; and I'll
take all the help I can get so that I don't create something that can't
grow to cover gaps I didn't konw I had.

Thanks again for all your help, sharing your knowledge and experience
means alot to those of us who are rather cluless.

Byron

I saw Jon Vinson's answer only after I posted my last response and I thought
it correctly diagnosed the problem. I guess I'm not sure what you're trying

[quoted text clipped - 53 lines]

 

[kingston via AccessMonster.com]

The button should run a procedure not a macro. When you create the button,
ignore the wizard (just hit cancel). Then in the button's properties window,
go to the Event tab and choose [Event Procedure] in the On Click box. Click
the ... button at the end of the line and type Me.Requery when Access opens
the VB window.

Kingston,

I did everything you said and once it was complete I entered a name and
hit the button. An error was returned stating that the macro Me was
not available. I'm using access 2000, should this macro be available?

Byron

See if this will work for you:

[quoted text clipped - 56 lines]

 

[Dos Equis]

It still doesn't like me. Once I use the ... button it put an = in
front of Me.Requery and agve me a different error that's basically
looking for an object, module or macro that is not defined.

Any other suggestions?

The button should run a procedure not a macro. When you create the button,
ignore the wizard (just hit cancel). Then in the button's properties window,
go to the Event tab and choose [Event Procedure] in the On Click box. Click
the ... button at the end of the line and type Me.Requery when Access opens
the VB window.

Kingston,

I did everything you said and once it was complete I entered a name and
hit the button. An error was returned stating that the macro Me was
not available. I'm using access 2000, should this macro be available?

Byron

See if this will work for you:

[quoted text clipped - 56 lines]

 

[kingston via AccessMonster.com]

There is no = in front of Me.Requery. The whole procedure should look
something like:

Private Sub Command0_Click()

Me.Requery

End Sub

It still doesn't like me. Once I use the ... button it put an = in
front of Me.Requery and agve me a different error that's basically
looking for an object, module or macro that is not defined.

Any other suggestions?

The button should run a procedure not a macro. When you create the button,
ignore the wizard (just hit cancel). Then in the button's properties window,

[quoted text clipped - 19 lines]

 

[kingston via AccessMonster.com]

OK, it seems that you're not creating a procedure. Go ahead and delete the
button you've created. Create a new button. If a Command Button Wizard
window appears, click Cancel. Right click on the button and open the
Properties window (last selection in the list). Go to the Event tab. In the
line for On Click, select [Event Procedure]. Click the ... button at the end
of that line in the Properties window. Type Me.Requery.

It still doesn't like me. Once I use the ... button it put an = in
front of Me.Requery and agve me a different error that's basically
looking for an object, module or macro that is not defined.

Any other suggestions?

The button should run a procedure not a macro. When you create the button,
ignore the wizard (just hit cancel). Then in the button's properties window,

[quoted text clipped - 19 lines]

 

[Dos Equis]

Still doesn't work. Not sure if my copy of access is jacked up or if
I'm not doing things right.

I deleted the button, created a new button, canceled the wizard,
RC,Properties, Event Tab, On Click is empty, ..., Pop-up window -
Choose Builder, exit that, arrow down on blank window and choose [Event
Procedure], type in Me.Query over it and then try to run it. Macro not
found.

If I'm doing something wrong, it should be in that description.

OK, it seems that you're not creating a procedure. Go ahead and delete the
button you've created. Create a new button. If a Command Button Wizard
window appears, click Cancel. Right click on the button and open the
Properties window (last selection in the list). Go to the Event tab. In the
line for On Click, select [Event Procedure]. Click the ... button at the end
of that line in the Properties window. Type Me.Requery.

It still doesn't like me. Once I use the ... button it put an = in
front of Me.Requery and agve me a different error that's basically
looking for an object, module or macro that is not defined.

Any other suggestions?

The button should run a procedure not a macro. When you create the button,
ignore the wizard (just hit cancel). Then in the button's properties window,

[quoted text clipped - 19 lines]

 

[kingston via AccessMonster.com]

When you reach this:

RC,Properties, Event Tab, On Click is empty

Select [Event Procedure] in the On Click box (put your cursor in the box, a
dropdown arrow will appear). Then press the ... button at the end of the
line, etc.

Still doesn't work. Not sure if my copy of access is jacked up or if
I'm not doing things right.

I deleted the button, created a new button, canceled the wizard,
RC,Properties, Event Tab, On Click is empty, ..., Pop-up window -
Choose Builder, exit that, arrow down on blank window and choose [Event
Procedure], type in Me.Query over it and then try to run it. Macro not
found.

If I'm doing something wrong, it should be in that description.

OK, it seems that you're not creating a procedure. Go ahead and delete the
button you've created. Create a new button. If a Command Button Wizard

[quoted text clipped - 18 lines]

 

[Dos Equis]

Ok, good news and bad...

Good - Once I did that, the code window poped up and allowed me to
input Me.Requery

I saved it and no error when clicked.

Bad - When I type a name in the box, it doesn't filter the query to
that name only.

I place a name in that box and press return, it moves to the next
record, I place a name in the box and click the button, returns to
record 1.

Any idea what's going on with that? Thanks for all your help,

Byron

When you reach this:

RC,Properties, Event Tab, On Click is empty

Select [Event Procedure] in the On Click box (put your cursor in the box, a
dropdown arrow will appear). Then press the ... button at the end of the
line, etc.

Still doesn't work. Not sure if my copy of access is jacked up or if
I'm not doing things right.

I deleted the button, created a new button, canceled the wizard,
RC,Properties, Event Tab, On Click is empty, ..., Pop-up window -
Choose Builder, exit that, arrow down on blank window and choose [Event
Procedure], type in Me.Query over it and then try to run it. Macro not
found.

If I'm doing something wrong, it should be in that description.

OK, it seems that you're not creating a procedure. Go ahead and delete the
button you've created. Create a new button. If a Command Button Wizard

[quoted text clipped - 18 lines]