Client Form

[Mike]

Can someone explain how to do this using the macro editor
in Access 2003 - I don't know any VBA

I have a client form with a subform for work to be done.
The client is idenitfied by an ID and there is another
form (embedded) for the services they have ordered.
(Each is on a seperate table). There are seven common
services (which are in a seperate LU_table).

I want to create a button on the client form that inputs
the seven common services ordred by clients. I was
thinking...

Open Form [FormServices]
Goto LastRecord??
Goto [Services]
Rename Service1
Goto NextRecord
Goto [services]
Rename Service2....

I don't know if this is even close to the right macro
language. I have no idea how to get to the form so that
it is based on the parentform. I have no idea how to get
to the last record. I have no idea how to "input" the
appropriate field from the LU_table. Hope this makes
sense. Please help.

Mike

 

[Steve Schapel]

Mike,

 

[Steve Schapel]

Mike,

The general concept of the method you have suggested is theoretically
possible, but in practice this is not a good approach. Instead I would
recommend:
1. make a Query based on your LU_Table table, and add the Service field
to the query design grid
2. make it into an Append Query (select Append from the Query menu in
the design view of the query), nominating the table that the subform is
based on as the table to append to
3. put the equivalent of the following into the Field row of a column
in the query design grid...
[Forms]![Client]![ID]
4. use an OpenQuery action in your macro to run this Append Query

 

[Mike]

I made the append query to the services table but it's
having trouble "grabing" the correct form. When I put:

[Forms]![Client]![ID]

into it's own field colomn it thinks it's a parameter to
enter. When I run the query as an append I get a key
violation.

I should mention that the main form is the client form
but there's a subform (services main sub) that is based
on a propertyID (which is a one to many with the
clientID). I tried the expression.

[Forms]![services main sub]![address]


Instead I would

recommend:
1. make a Query based on your LU_Table table, and add the Service field
to the query design grid
2. make it into an Append Query (select Append from the Query menu in
the design view of the query), nominating the table that the subform is
based on as the table to append to
3. put the equivalent of the following into the Field row of a column
in the query design grid...
[Forms]![Client]![ID]
4. use an OpenQuery action in your macro to run this Append Query

--
Steve Schapel, Microsoft Access MVP

Can someone explain how to do this using the macro editor
in Access 2003 - I don't know any VBA

I have a client form with a subform for work to be done.
The client is idenitfied by an ID and there is another
form (embedded) for the services they have ordered.
(Each is on a seperate table). There are seven common
services (which are in a seperate LU_table).

I want to create a button on the client form that inputs
the seven common services ordred by clients. I was
thinking...

Open Form [FormServices]
Goto LastRecord??
Goto [Services]
Rename Service1
Goto NextRecord
Goto [services]
Rename Service2....

I don't know if this is even close to the right macro
language. I have no idea how to get to the form so that
it is based on the parentform. I have no idea how to get
to the last record. I have no idea how to "input" the
appropriate field from the LU_table. Hope this makes
sense. Please help.

Mike

.

 

[Steve Schapel]

Mike

Is the name of the main form 'Client'? Is the name of the primary key
field 'ID' or is it 'ClientID'? Please go to the query design and from
the View menu select SQL and then copy/paste the SQL of the query into
your reply, and I will see if I can spot the problem.

--
Steve Schapel, Microsoft Access MVP

I made the append query to the services table but it's
having trouble "grabing" the correct form. When I put:

[Forms]![Client]![ID]

into it's own field colomn it thinks it's a parameter to
enter. When I run the query as an append I get a key
violation.

I should mention that the main form is the client form
but there's a subform (services main sub) that is based
on a propertyID (which is a one to many with the
clientID). I tried the expression.

[Forms]![services main sub]![address]

 

[Steve Schapel]

Mike

I have just seen your other post, but will respond here to avoid
confusion with two separate discussions.

The purpose of the Append is to put the selected services from the
LU_table table (there was no need to make a separate query as an
intermediate step here, by the way), plus the ID field from the main
form, whatever it's called, into the Services and PropertyID fields of
the services table.

As mentioned in my other reply, let's have a look at the SQL of both the
queries, and we'll work it out.

 

[Mike]

Here is e SQL view

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT LU_Hot_button1.[Service Name], forms![services
main sub]!propertyid AS Expr1
FROM LU_Hot_button1;

LU_Hot_button1 is the select query based on LU_services
that selects out the 7 services I want inserted by the
button.

Expr1 is the [forms]![services main sub]![propertyID]

The propertyID is the joined field between the services
table and the property table. I assume the append needs
to grab the property ID from the services main sub form
and append it to the services table with the seven
services I wanted from the LU_table.

Thanks for yur deligence.

 

[Steve Schapel]

Mike,

No this is not correct. We are getting closer to the truth, but you
need to be referring to the ID field whatever it's called on the main
form whatever it's called. PropertyID on the subform is the linking
field between the main form and the subform, right? So what's the
linking field on the main form?

Can I also see the SQL view of LU_Hot_button1 query?

 

[Mike]

OK first here is the SQL for the hot button1
SELECT LU_Services.ServiceID, LU_Services.[Service Name]
FROM LU_Services
GROUP BY LU_Services.ServiceID, LU_Services.[Service Name]
HAVING (((LU_Services.ServiceID)=5 Or
(LU_Services.ServiceID)=6 Or (LU_Services.ServiceID)=7 Or
(LU_Services.ServiceID)=8 Or (LU_Services.ServiceID)=9 Or
(LU_Services.ServiceID)=10))
ORDER BY LU_Services.ServiceID;

just a simple select query to isolate the services I want
added.

There are three tables involved on three forms (one main
form with two embedded)
The three tables are client, property and services
ID is the main clientID and joins to property.
PropertyID is the uniquekey for each property and
servicesID is uniquekey for each service ordered.
The form
"client info" is opened and on a tab I have put
the "services main sub" which lists the active address
(with address and property ID). Embedded in the "services
main sub" is the "service record subform" which shows the
services ordred for the property listed in the "services
main sub".

Does this help?

-----Original Message-----
Mike,

No this is not correct. We are getting closer to the truth, but you
need to be referring to the ID field whatever it's called on the main
form whatever it's called. PropertyID on the subform is the linking
field between the main form and the subform, right? So what's the
linking field on the main form?

Can I also see the SQL view of LU_Hot_button1 query?

--
Steve Schapel, Microsoft Access MVP

Here is e SQL view

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT LU_Hot_button1.[Service Name], forms![services
main sub]!propertyid AS Expr1
FROM LU_Hot_button1;

LU_Hot_button1 is the select query based on LU_services
that selects out the 7 services I want inserted by the
button.

Expr1 is the [forms]![services main sub]![propertyID]

The propertyID is the joined field between the services
table and the property table. I assume the append needs
to grab the property ID from the services main sub form
and append it to the services table with the seven
services I wanted from the LU_table.

Thanks for yur deligence.

.

 

[Steve Schapel]

Mike,

Yes, it does help. Enormously. I would never have guessed that the
services subform is a subform of another subform. Well, therefore you
can't refer to [forms]![services main sub]![propertyID] in the
expression because the [services main sub] form is not open. If I
understand you correctly, try something like this...

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT [Service Name], [Forms]![client info]![services main
sub]![propertyID] AS Expr1
FROM LU_Services
WHERE [ServiceID] In(5,6,7,8,9,10)

 

[Mike]

Still doesn't seem to help - it keeps prompting for me to
enter the parameter for expr1. I can only assume I've
named the path to the field propertyID incorrectly?

I went back to look at things. The subforms are open on
the main form (just hit a tab control and it's in it's
own window) so I think it should see it. I looked at the
subform "services main sub" and it definately has the
propertyID on it but... when I look at the properties
section the caption is set to "services" - so I change it
to expr1: [forms]![client info]![services]![propertyid]
and still get the same message.

....has converted 6 records to Null due to type conversion
failure.... which results in key violations

Here's some more info...
The main form is "client info"
The tab is "all services"
The subform is "services main sub" but I've relabelled
the caption in properties as "services" - this has the
propertyID field. (there is no form saved as services)
The subform that is getting the records posted to it is
service record subform - but I don't really see how this
matters because I'm appending to the table not the form
correct?

The biggest problem still seems to be with that expr1.

-----Original Message-----
Mike,

Yes, it does help. Enormously. I would never have guessed that the
services subform is a subform of another subform. Well, therefore you
can't refer to [forms]![services main sub]![propertyID] in the
expression because the [services main sub] form is not open. If I
understand you correctly, try something like this...

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT [Service Name], [Forms]![client info]![services main
sub]![propertyID] AS Expr1
FROM LU_Services
WHERE [ServiceID] In(5,6,7,8,9,10)

--
Steve Schapel, Microsoft Access MVP

OK first here is the SQL for the hot button1
SELECT LU_Services.ServiceID, LU_Services.[Service Name]
FROM LU_Services
GROUP BY LU_Services.ServiceID, LU_Services.[Service Name]
HAVING (((LU_Services.ServiceID)=5 Or
(LU_Services.ServiceID)=6 Or (LU_Services.ServiceID)=7 Or
(LU_Services.ServiceID)=8 Or (LU_Services.ServiceID)=9 Or
(LU_Services.ServiceID)=10))
ORDER BY LU_Services.ServiceID;

just a simple select query to isolate the services I want
added.

There are three tables involved on three forms (one main
form with two embedded)
The three tables are client, property and services
ID is the main clientID and joins to property.
PropertyID is the uniquekey for each property and
servicesID is uniquekey for each service ordered.
The form
"client info" is opened and on a tab I have put
the "services main sub" which lists the active address
(with address and property ID). Embedded in the "services
main sub" is the "service record subform" which shows the
services ordred for the property listed in the "services
main sub".

Does this help?

.

 

[Mike]

One other bit of info - when I save the append query it
does strange things to the expression.

I write it Expr1: [forms]![client info]![services main
sub]![propertyID]

but when I save it and reopen it I see:
Expr1: forms![client info]!services main sub!propertyID

which makes me think it doesn't recognize the expression
as a reference to an open form, rather it thinks it's a
parameter.

When I write it out and check sql view here is what I get:
INSERT INTO [Service Record] ( Service, PropertyID )
SELECT LU_Services.[Service Name], [ forms]![client info]!
[services main sub]![propertyID] AS Expr1
FROM LU_Services
WHERE (((LU_Services.ServiceID) In (5,6,7,8,9,10)));

When I save it then reopen it in sql view I get:

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT LU_Services.[Service Name], [ forms]![client info]!
[services main sub]!propertyID AS Expr1
FROM LU_Services
WHERE (((LU_Services.ServiceID) In (5,6,7,8,9,10)));

Notice the propertyID is no longer in brackets

-----Original Message-----
Mike,

Yes, it does help. Enormously. I would never have guessed that the
services subform is a subform of another subform. Well, therefore you
can't refer to [forms]![services main sub]![propertyID] in the
expression because the [services main sub] form is not open. If I
understand you correctly, try something like this...

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT [Service Name], [Forms]![client info]![services main
sub]![propertyID] AS Expr1
FROM LU_Services
WHERE [ServiceID] In(5,6,7,8,9,10)

--
Steve Schapel, Microsoft Access MVP

OK first here is the SQL for the hot button1
SELECT LU_Services.ServiceID, LU_Services.[Service Name]
FROM LU_Services
GROUP BY LU_Services.ServiceID, LU_Services.[Service Name]
HAVING (((LU_Services.ServiceID)=5 Or
(LU_Services.ServiceID)=6 Or (LU_Services.ServiceID)=7 Or
(LU_Services.ServiceID)=8 Or (LU_Services.ServiceID)=9 Or
(LU_Services.ServiceID)=10))
ORDER BY LU_Services.ServiceID;

just a simple select query to isolate the services I want
added.

There are three tables involved on three forms (one main
form with two embedded)
The three tables are client, property and services
ID is the main clientID and joins to property.
PropertyID is the uniquekey for each property and
servicesID is uniquekey for each service ordered.
The form
"client info" is opened and on a tab I have put
the "services main sub" which lists the active address
(with address and property ID). Embedded in the "services
main sub" is the "service record subform" which shows the
services ordred for the property listed in the "services
main sub".

Does this help?

.

 

[Mike]

sorry - also ignore the space in front of the forms - [
forms] - i corrected that and it still won't work but it
does cause access to automatically remove my square
brackets!

-----Original Message-----
One other bit of info - when I save the append query it
does strange things to the expression.

I write it Expr1: [forms]![client info]![services main
sub]![propertyID]

but when I save it and reopen it I see:
Expr1: forms![client info]!services main sub!propertyID

which makes me think it doesn't recognize the expression
as a reference to an open form, rather it thinks it's a
parameter.

When I write it out and check sql view here is what I get:
INSERT INTO [Service Record] ( Service, PropertyID )
SELECT LU_Services.[Service Name], [ forms]![client info]!
[services main sub]![propertyID] AS Expr1
FROM LU_Services
WHERE (((LU_Services.ServiceID) In (5,6,7,8,9,10)));

When I save it then reopen it in sql view I get:

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT LU_Services.[Service Name], [ forms]![client info]!
[services main sub]!propertyID AS Expr1
FROM LU_Services
WHERE (((LU_Services.ServiceID) In (5,6,7,8,9,10)));

Notice the propertyID is no longer in brackets

-----Original Message-----
Mike,

Yes, it does help. Enormously. I would never have guessed that the
services subform is a subform of another subform.

Well,
therefore you

can't refer to [forms]![services main sub]![propertyID] in the
expression because the [services main sub] form is not open. If I
understand you correctly, try something like this...

INSERT INTO [Service Record] ( Service, PropertyID )
SELECT [Service Name], [Forms]![client info]![services main
sub]![propertyID] AS Expr1
FROM LU_Services
WHERE [ServiceID] In(5,6,7,8,9,10)

--
Steve Schapel, Microsoft Access MVP

OK first here is the SQL for the hot button1
SELECT LU_Services.ServiceID, LU_Services.[Service Name]
FROM LU_Services
GROUP BY LU_Services.ServiceID, LU_Services.[Service Name]
HAVING (((LU_Services.ServiceID)=5 Or
(LU_Services.ServiceID)=6 Or (LU_Services.ServiceID)

=7

Or

=9

Or
.

.

 

[Steve Schapel]

Mike,

I don't think Access's automatic removal of the []s around propertyID is
likely to be a problem here. However, I notice a space between the [
and the word Forms, and this will definitely cause a problem, should be
[Forms] not [ forms]. Other than that, if you are getting a parameter
prompt, the most likely causes are a spelling error, or else the [client
info] form is not open at the time. Also, I have assumed that the
subform control is named [services main sub] the same as the name of the
form which it contains. If you still have problems, check this, and if
it is different, try putting the name of the subform control intot the
query expression.

 

[Steve Schapel]

Mike,

The Caption property will not be relevant, but as mentioned in my ither
reply, the Name property of the subform control on the main form may be
relevant.

 

[Mike]

SUCCESS - it wanted the name of the table that propertyID
was in not the name of the form - who knows why - who
cares. Anyway, it also had a problem getting the name of
the service in (I used the look-up feature in table
design rather than doing it properly in my form). As a
result I had to put in the uniqueID from the LU_table.

Anyway - if you live anywhere near Waterloo, Ontario,
Canada the beers are on me (wings too - that was a long
one). Thank you so much.

 

[Steve Schapel]

Very good, Mike. I am very happy to know you have finally cracked it.
Hopefully it will be a bit easier for you next time you have to do
something similar. As regards the Lookup field problem, you may find
this interesting...
http://www.mvps.org/access/lookupfields.htm