Please help..I'm going barmy !

M

MitziUK

I have built a database to record all details regarding our company vehicles,
including mileage, servicing details, maintenance, mot & tax data plus
miscellaneous items.

I am having extreme problems building one part of this database (namely
servicing). I have posted a few items on the Microsoft Support Group, and
have numerous responses none of which helped me to resolve my problem;
however this is probably due to my poor explanation of my problem. It is
here therefore that I have resulted to posting my problem by hyper-linking to
a website where you can see visual images of my database structure and forms.
Hopefully it will give you a better idea of what I am trying to achieve.
Please note I am by no means trying to market the contents of the website,
just purely using its space to host my problem so that you can access it.

click the link below for more detailed info on my problem (word document,
showing screen images).

http://www.odessafarm.co.uk/Misc/database writeup.doc
 
S

Sarah

Mitzi
is your main form just based on 1 table, ie company vehicle table? If you
based it on a query which used tables-company vehicle and -service intervals,
you can just use a simple control for Service Int Miles-Hours on the form.
Use the wizard to make the query, since it will automatically link in all the
tables neded for relationships, even if you don't want any info from them.

By the way, I'd love to know how you do tabs on forms. If pos could you
e-mail me your DB file?
 
M

MitziUK

Sarah

Tabs on forms are achieved by using the tab control button found on the form
toolbar.
 
M

MitziUK

I've now based the text box on a query and I get #Name? appear. I cannot
understand why? I have checked that the relationships are there, the related
fields
are the same tpe etc.

Would someone mind checking it out and point out any glaring errors?
 
M

MacDermott

Without looking at your database, I might mention that one common cause of
#Name is that the name of the control is the same as the name of a field in
the underlying query, but the ControlSource for that control is not the same
field.

BTW, how do you base a text box on a query? Wouldn't it be the entire form
that's based on the query?
 
W

Willow

In looking at your code, is MakeID a text Field? if so you would need quotes
around your variable
=DLookUp("[ServiceIntMiles-Hours]","[Service Intervals]","[MakeID] = '" &
[MakeID] & "'")
 
M

MitziUK

Created the query. displayed a text box on the form. name of text box =
text65 in the control source row it says =[Service Intervals
Query]![ServiceIntMiles-Hours].

but still displays #Name?

?????? Arrggggh
 
M

MitziUK

Willow

No this Make ID fields are number fields. I entered your code into the
contro source and the text box is now blank, no errors reported but no data
display either?

Willow said:
In looking at your code, is MakeID a text Field? if so you would need quotes
around your variable
=DLookUp("[ServiceIntMiles-Hours]","[Service Intervals]","[MakeID] = '" &
[MakeID] & "'")

--
Willow
Expert Access User


MacDermott said:
Without looking at your database, I might mention that one common cause of
#Name is that the name of the control is the same as the name of a field in
the underlying query, but the ControlSource for that control is not the same
field.

BTW, how do you base a text box on a query? Wouldn't it be the entire form
that's based on the query?
Click to expand...
Click to expand...
 
W

Willow

Mitziuk:

I noticed that in the table [Service Intervals], the field name that you are
calling MakeID should be written as [Make ID]. I think that is your issue.
Then written this way.

=DLookUp("[ServiceIntMiles-Hours]","[Service Intervals]","[Make ID] = " &
[MakeID])

Spelling can be the biggest issue when using Dlookups.
Hope that helps!!

Willow
Expert Access User


MitziUK said:
Willow

No this Make ID fields are number fields. I entered your code into the
contro source and the text box is now blank, no errors reported but no data
display either?

Willow said:
In looking at your code, is MakeID a text Field? if so you would need quotes
around your variable
=DLookUp("[ServiceIntMiles-Hours]","[Service Intervals]","[MakeID] = '" &
[MakeID] & "'")

--
Willow
Expert Access User


MacDermott said:
Without looking at your database, I might mention that one common cause of
#Name is that the name of the control is the same as the name of a field in
the underlying query, but the ControlSource for that control is not the same
field.

BTW, how do you base a text box on a query? Wouldn't it be the entire form
that's based on the query?

I've now based the text box on a query and I get #Name? appear. I cannot
understand why? I have checked that the relationships are there, the
related
fields
are the same tpe etc.

Would someone mind checking it out and point out any glaring errors?

:

Mitzi
is your main form just based on 1 table, ie company vehicle table? If
you
based it on a query which used tables-company vehicle and -service
intervals,
you can just use a simple control for Service Int Miles-Hours on the
form.
Use the wizard to make the query, since it will automatically link in
all the
tables neded for relationships, even if you don't want any info from
them.

Regards
Sarah


:

Sorry heres the link again

http://www.odessafarm.co.uk/Misc/databasewriteup.doc
Click to expand...
Click to expand...
Click to expand...
 
M

MitziUK

THANK YOU THANK YOU THANK YOU THANK YOU.

I knew it would be something so obvious. I guess when time is against you
to complete the database,management on your back you don't see the obvious.

THANK YOU

Willow said:
Mitziuk:

I noticed that in the table [Service Intervals], the field name that you are
calling MakeID should be written as [Make ID]. I think that is your issue.
Then written this way.

=DLookUp("[ServiceIntMiles-Hours]","[Service Intervals]","[Make ID] = " &
[MakeID])

Spelling can be the biggest issue when using Dlookups.
Hope that helps!!

Willow
Expert Access User


MitziUK said:
Willow

No this Make ID fields are number fields. I entered your code into the
contro source and the text box is now blank, no errors reported but no data
display either?

Willow said:
In looking at your code, is MakeID a text Field? if so you would need quotes
around your variable
=DLookUp("[ServiceIntMiles-Hours]","[Service Intervals]","[MakeID] = '" &
[MakeID] & "'")

--
Willow
Expert Access User


:

Without looking at your database, I might mention that one common cause of
#Name is that the name of the control is the same as the name of a field in
the underlying query, but the ControlSource for that control is not the same
field.

BTW, how do you base a text box on a query? Wouldn't it be the entire form
that's based on the query?

I've now based the text box on a query and I get #Name? appear. I cannot
understand why? I have checked that the relationships are there, the
related
fields
are the same tpe etc.

Would someone mind checking it out and point out any glaring errors?

:

Mitzi
is your main form just based on 1 table, ie company vehicle table? If
you
based it on a query which used tables-company vehicle and -service
intervals,
you can just use a simple control for Service Int Miles-Hours on the
form.
Use the wizard to make the query, since it will automatically link in
all the
tables neded for relationships, even if you don't want any info from
them.

Regards
Sarah


:

Sorry heres the link again

http://www.odessafarm.co.uk/Misc/databasewriteup.doc
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

I might suggest changing form's recordsource
to following query:

SELECT
[Service Intervals].[ServiceIntMiles-Hours],
[Vans & Cars].[REGISTRATION NUMBER],
[Vans & Cars].[ENGINE SERIAL NUMBER],
[Vans & Cars].VIN_CHASSIS_FRAMENO,
[Vans & Cars].MAKEID,
[Vans & Cars].ModelID,
[Vans & Cars].CC,
[Vans & Cars].YEAR,
[Vans & Cars].DT_PURCHASE,
[Vans & Cars].DT_SOLD,
[Vans & Cars].DT_SORN,
[Vans & Cars].RADIO_CODE,
[Vans & Cars].NOTES,
[Vans & Cars].[Driver:]
FROM [Vans & Cars] LEFT JOIN [Service Intervals]
ON [Vans & Cars].MAKEID = [Service Intervals].[Make ID]
WHERE ((([Vans & Cars].DT_SOLD) Is Null));

At least try it so you can see what you were trying
to get --- did all exist?

Queries are good...but domain functions always give
a "ker-thunk" action to forms. If you can, always
try to use queries. Domain functions in a form almost
always mean I haven't thought through design enough
(they tend to mean "oh, as an afterthought, wouldn't
it be great if I could show xxxx...")

Just some further thoughts about your db (meaning no offense)...

- "YEAR" is a reserved word and will probably "getcha"
somewhere down the road

- I just don't think using punctuation ("&","-", etc)
in a field/table name is a good idea, nor SPACES.
Besides meaning you have to constantly surround
them with brackets, you won't have to think...

in [Vans & Cars] it is "MAKEID"
but in [Service Intervals], it is "Make ID"

- When I look at your Relationships, I have
to wonder why you don't have just a single
"Vehicles" table, with a field for "VehicleType"
and a field for PlantNo (so you can make one
table out of 3 that appear to store redundant info).

good luck,

gary
 
M

MitziUK

Gary

No offence taken. thanks for looking over my database. It's actually the
first one I've ever done, before this I'd only ever used access to do some
minor analysis work. SQL is a new thing for me so eventually I intend to get
my head around it. Doing this database has been a huge learning curve too,
so your comments are welcomed.

Thanks. :cool:

Gary Walter said:
I might suggest changing form's recordsource
to following query:

SELECT
[Service Intervals].[ServiceIntMiles-Hours],
[Vans & Cars].[REGISTRATION NUMBER],
[Vans & Cars].[ENGINE SERIAL NUMBER],
[Vans & Cars].VIN_CHASSIS_FRAMENO,
[Vans & Cars].MAKEID,
[Vans & Cars].ModelID,
[Vans & Cars].CC,
[Vans & Cars].YEAR,
[Vans & Cars].DT_PURCHASE,
[Vans & Cars].DT_SOLD,
[Vans & Cars].DT_SORN,
[Vans & Cars].RADIO_CODE,
[Vans & Cars].NOTES,
[Vans & Cars].[Driver:]
FROM [Vans & Cars] LEFT JOIN [Service Intervals]
ON [Vans & Cars].MAKEID = [Service Intervals].[Make ID]
WHERE ((([Vans & Cars].DT_SOLD) Is Null));

At least try it so you can see what you were trying
to get --- did all exist?

Queries are good...but domain functions always give
a "ker-thunk" action to forms. If you can, always
try to use queries. Domain functions in a form almost
always mean I haven't thought through design enough
(they tend to mean "oh, as an afterthought, wouldn't
it be great if I could show xxxx...")

Just some further thoughts about your db (meaning no offense)...

- "YEAR" is a reserved word and will probably "getcha"
somewhere down the road

- I just don't think using punctuation ("&","-", etc)
in a field/table name is a good idea, nor SPACES.
Besides meaning you have to constantly surround
them with brackets, you won't have to think...

in [Vans & Cars] it is "MAKEID"
but in [Service Intervals], it is "Make ID"

- When I look at your Relationships, I have
to wonder why you don't have just a single
"Vehicles" table, with a field for "VehicleType"
and a field for PlantNo (so you can make one
table out of 3 that appear to store redundant info).

good luck,

gary

I have built a database to record all details regarding our company
vehicles,
including mileage, servicing details, maintenance, mot & tax data plus
miscellaneous items.

I am having extreme problems building one part of this database (namely
servicing). I have posted a few items on the Microsoft Support Group, and
have numerous responses none of which helped me to resolve my problem;
however this is probably due to my poor explanation of my problem. It is
here therefore that I have resulted to posting my problem by hyper-linking
to
a website where you can see visual images of my database structure and
forms.
Hopefully it will give you a better idea of what I am trying to achieve.
Please note I am by no means trying to market the contents of the website,
just purely using its space to host my problem so that you can access it.

click the link below for more detailed info on my problem (word document,
showing screen images).

http://www.odessafarm.co.uk/Misc/database writeup.doc
Click to expand...
Click to expand...
 
Top