Can't figure this one out

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

kingston via AccessMonster.com wrote:
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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via http://www.accessmonster.com

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

kingston via AccessMonster.com wrote:
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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via http://www.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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

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?



kingston via AccessMonster.com wrote:
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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

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

Private Sub Command0_Click()

Me.Requery

End Sub


Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via http://www.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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

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.


kingston via AccessMonster.com wrote:
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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

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


kingston via AccessMonster.com wrote:
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.

Dos Equis wrote:
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]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200611/1