Call database property in a control on a form and in my Switchboard variables

[Jp4]

I have a database that I have developed for multiple units to use. For
reasons that I won't go into, each unit must have an individual copy
of the database to store their data. BUT the databases need to be
identical. So far, annoying, but no big deal.

Anyway, what I am doing is creating the master copy where I get it all
correct and then I will make a copy for each unit and drop their data
into it... again no big deal. BUT in order to help keep them straight,
I would like to have specific labels that refer to the database
property Title bc the DB is on a network buried in a file more then 10
levels down so using the name is unrealistic.

I have used the Switchboard Manager to create my switchboard (I know,
don't shoot me, let's just move forward with that knowledge There
is currently a label at the top that says "Unit Tracking." I want it
to say "[DatabaseProperty.Title] Tracking" How would I go about
either:
1) making the Label Caption read that property or 2) after I change it
to a textbox, referring to it as part of the control source? I just
can't seem to find the right references for the second version.
CurrentDB.Name did give me the file name but again it was too long
because that gave me the whole path and I don't want to use the
filename anyway in case the users decide to change it. I can
individually go through all the labels. I was just hoping to do it
programatically if I could, espeically since unit names change often
enough to be a nuisance. If it were just the matter of the Switchboard
form I would do it manually but it actually is something that the
"powers that be" want on all the forms and there are quite a number.

Also in my Switchboard Items table, I would also like this same
information injected into the a couple of the ItemText labels. This is
less necessary though.

for example I have: SwitchboardID=1, ItemNumber=1, ItemText=Exit
Tracking Database, Command=6,Argument=(Null) but I want:
SwitchboardID=1, ItemNumber=1, ItemText=Exit
*DatabaseProperties.Title* Tracking Database,
Command=6,Argument=(Null).

Again this second issue is less necessary and I am less interested,
especially if the other process works.

Thanks in advance.

 

[Guest]

Probably best to have a table dedicated to keeping this kind of
information. Single row, one field for each item.

You can bind the table to a form if you want.

If you use a split database, you can put the config table in
the back end - and your title will tell you that you are
connected to the correct database.

You can use DLookup to get you title out of the table.
You can concatenate the whole DLookup into any
expression of property that you want - it is a function,
so you can use =Dlookup(...) in your form properties
if you want.

You can use a database property if you want. The database
title property is a good one to use. and you may want to use
it as well anyway. Go to Tools,Startup,
and fill in an Application Title. Then you can use it in
VBA and in form properties,
= codedb.Properties("AppTitle")

and it will also always be displayed in the application title bar.

(david)

I have a database that I have developed for multiple units to use. For
reasons that I won't go into, each unit must have an individual copy
of the database to store their data. BUT the databases need to be
identical. So far, annoying, but no big deal.

Anyway, what I am doing is creating the master copy where I get it all
correct and then I will make a copy for each unit and drop their data
into it... again no big deal. BUT in order to help keep them straight,
I would like to have specific labels that refer to the database
property Title bc the DB is on a network buried in a file more then 10
levels down so using the name is unrealistic.

I have used the Switchboard Manager to create my switchboard (I know,
don't shoot me, let's just move forward with that knowledge There
is currently a label at the top that says "Unit Tracking." I want it
to say "[DatabaseProperty.Title] Tracking" How would I go about
either:
1) making the Label Caption read that property or 2) after I change it
to a textbox, referring to it as part of the control source? I just
can't seem to find the right references for the second version.
CurrentDB.Name did give me the file name but again it was too long
because that gave me the whole path and I don't want to use the
filename anyway in case the users decide to change it. I can
individually go through all the labels. I was just hoping to do it
programatically if I could, espeically since unit names change often
enough to be a nuisance. If it were just the matter of the Switchboard
form I would do it manually but it actually is something that the
"powers that be" want on all the forms and there are quite a number.

Also in my Switchboard Items table, I would also like this same
information injected into the a couple of the ItemText labels. This is
less necessary though.

for example I have: SwitchboardID=1, ItemNumber=1, ItemText=Exit
Tracking Database, Command=6,Argument=(Null) but I want:
SwitchboardID=1, ItemNumber=1, ItemText=Exit
*DatabaseProperties.Title* Tracking Database,
Command=6,Argument=(Null).

Again this second issue is less necessary and I am less interested,
especially if the other process works.

Thanks in advance.

 

[Jp4]

Thanks. I got it to work for my Switchboard title by making it the
control source of an unbound Textbox where the control source

=(codedb.Properties("AppTitle"))&" Product Tracking"

but that method does not appear to be working in my reports, either as
a label or an unbound textbox. What am I doing wrong? I seem to have
a problem when I want to combine text and fields in a text box like
="Reports for "&[FieldName]. Are these problems related, or two
totally different things? I did get this method to work on the Form so
I am wondering if it a Report thing. I know I can do it on a report
because I have a report with it, but sometimes I can't get that method
to work on other reports.

Also, What do you mean by a split database? I am thinking I might be
missing out on something... ok I read a little and get the gist of
front end/back end. Can I make it so that I have multiple front ends
that can only see specific records based on certain criteria, such as,
let's say, the codedb.Properties("AppTitle")?? Is this how to have an
Access front end and a mySQL backend maybe... This is a goal of mine,
someday, when I have time to learn mySQL.

Thanks.

Probably best to have a table dedicated to keeping this kind of
information. Single row, one field for each item.

You can bind the table to a form if you want.

If you use a split database, you can put the config table in
the back end - and your title will tell you that you are
connected to the correct database.

You can use DLookup to get you title out of the table.
You can concatenate the whole DLookup into any
expression of property that you want - it is a function,
so you can use =Dlookup(...) in your form properties
if you want.

You can use a database property if you want. The database
title property is a good one to use. and you may want to use
it as well anyway. Go to Tools,Startup,
and fill in an Application Title. Then you can use it in
VBA and in form properties,
= codedb.Properties("AppTitle")

and it will also always be displayed in the application title bar.

(david)

I have a database that I have developed for multiple units to use. For
reasons that I won't go into, each unit must have an individual copy
of the database to store their data. BUT the databases need to be
identical. So far, annoying, but no big deal.

Anyway, what I am doing is creating the master copy where I get it all
correct and then I will make a copy for each unit and drop their data
into it... again no big deal. BUT in order to help keep them straight,
I would like to have specific labels that refer to the database
property Title bc the DB is on a network buried in a file more then 10
levels down so using the name is unrealistic.

I have used the Switchboard Manager to create my switchboard (I know,
don't shoot me, let's just move forward with that knowledge There
is currently a label at the top that says "Unit Tracking." I want it
to say "[DatabaseProperty.Title] Tracking" How would I go about
either:
1) making the Label Caption read that property or 2) after I change it
to a textbox, referring to it as part of the control source? I just
can't seem to find the right references for the second version.
CurrentDB.Name did give me the file name but again it was too long
because that gave me the whole path and I don't want to use the
filename anyway in case the users decide to change it. I can
individually go through all the labels. I was just hoping to do it
programatically if I could, espeically since unit names change often
enough to be a nuisance. If it were just the matter of the Switchboard
form I would do it manually but it actually is something that the
"powers that be" want on all the forms and there are quite a number.

Also in my Switchboard Items table, I would also like this same
information injected into the a couple of the ItemText labels. This is
less necessary though.

for example I have: SwitchboardID=1, ItemNumber=1, ItemText=Exit
Tracking Database, Command=6,Argument=(Null) but I want:
SwitchboardID=1, ItemNumber=1, ItemText=Exit
*DatabaseProperties.Title* Tracking Database,
Command=6,Argument=(Null).

Again this second issue is less necessary and I am less interested,
especially if the other process works.

Thanks in advance.- Hide quoted text -

- Show quoted text -

 

[Jp4]

Oops... It doesn't work when the form is first opened. I need to go to
the design view and then back to form view before it will work.

Thanks. I got it to work for my Switchboard title by making it the
control source of an unbound Textbox where the control source

=(codedb.Properties("AppTitle"))&" Product Tracking"

but that method does not appear to be working in my reports, either as
a label or an unbound textbox. What am I doing wrong? I seem to have
a problem when I want to combine text and fields in a text box like
="Reports for "&[FieldName]. Are these problems related, or two
totally different things? I did get this method to work on the Form so
I am wondering if it a Report thing. I know I can do it on a report
because I have a report with it, but sometimes I can't get that method
to work on other reports.

Also, What do you mean by a split database? I am thinking I might be
missing out on something... ok I read a little and get the gist of
front end/back end. Can I make it so that I have multiple front ends
that can only see specific records based on certain criteria, such as,
let's say, the codedb.Properties("AppTitle")?? Is this how to have an
Access front end and a mySQL backend maybe... This is a goal of mine,
someday, when I have time to learn mySQL.

Thanks.

Probably best to have a table dedicated to keeping this kind of
information. Single row, one field for each item.

You can bind the table to a form if you want.

If you use a split database, you can put the config table in
the back end - and your title will tell you that you are
connected to the correct database.

You can use DLookup to get you title out of the table.
You can concatenate the whole DLookup into any
expression of property that you want - it is a function,
so you can use =Dlookup(...) in your form properties
if you want.

You can use a database property if you want. The database
title property is a good one to use. and you may want to use
it as well anyway. Go to Tools,Startup,
and fill in an Application Title. Then you can use it in
VBA and in form properties,
= codedb.Properties("AppTitle")

and it will also always be displayed in the application title bar.

(david)

news:[email protected]...

I have a database that I have developed for multiple units to use. For
reasons that I won't go into, each unit must have an individual copy
of the database to store their data. BUT the databases need to be
identical. So far, annoying, but no big deal.
Anyway, what I am doing is creating the master copy where I get it all
correct and then I will make a copy for each unit and drop their data
into it... again no big deal. BUT in order to help keep them straight,
I would like to have specific labels that refer to the database
property Title bc the DB is on a network buried in a file more then 10
levels down so using the name is unrealistic.
I have used the Switchboard Manager to create my switchboard (I know,
don't shoot me, let's just move forward with that knowledge There
is currently a label at the top that says "Unit Tracking." I want it
to say "[DatabaseProperty.Title] Tracking" How would I go about
either:
1) making the Label Caption read that property or 2) after I change it
to a textbox, referring to it as part of the control source? I just
can't seem to find the right references for the second version.
CurrentDB.Name did give me the file name but again it was too long
because that gave me the whole path and I don't want to use the
filename anyway in case the users decide to change it. I can
individually go through all the labels. I was just hoping to do it
programatically if I could, espeically since unit names change often
enough to be a nuisance. If it were just the matter of the Switchboard
form I would do it manually but it actually is something that the
"powers that be" want on all the forms and there are quite a number.
Also in my Switchboard Items table, I would also like this same
information injected into the a couple of the ItemText labels. This is
less necessary though.
for example I have: SwitchboardID=1, ItemNumber=1, ItemText=Exit
Tracking Database, Command=6,Argument=(Null) but I want:
SwitchboardID=1, ItemNumber=1, ItemText=Exit
*DatabaseProperties.Title* Tracking Database,
Command=6,Argument=(Null).
Again this second issue is less necessary and I am less interested,
especially if the other process works.
Thanks in advance.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Guest]

the design view and then back to form view before it will work.

That's odd. Are you using 2007 or 2003? Or perhaps you
haven't refreshed the app title after changing it? (That won't
be a problem once you have set it to the correct value).

If it really is a problem in the Access version you are using,
you will have to put the expression into the load event of
the form:
me.mytextbox.value = (codedb.Properties("AppTitle"))&" Product Tracking"
or
me.mylabel.caption=(codedb.Properties("AppTitle"))&" Product Tracking"

For a report, if you are using a query instead of a table
it may be easier to include the expression in the query source
of the report as a calculated field:
mytitle: (codedb.Properties("AppTitle"))&" Product Tracking"
or
(codedb.Properties("AppTitle"))&" Product Tracking" as mytiltle

It is possible to include a front end identifier in the selection
criteria of queries. It means that you have to include the identifier
in the record when you save or update the record. If you plan
to use mySQL, you might think of creating a different view
in mySQL for each front end,and just attaching the front end to
the correct view.

(david)

Oops... It doesn't work when the form is first opened. I need to go to
the design view and then back to form view before it will work.

Thanks. I got it to work for my Switchboard title by making it the
control source of an unbound Textbox where the control source

=(codedb.Properties("AppTitle"))&" Product Tracking"

but that method does not appear to be working in my reports, either as
a label or an unbound textbox. What am I doing wrong? I seem to have
a problem when I want to combine text and fields in a text box like
="Reports for "&[FieldName]. Are these problems related, or two
totally different things? I did get this method to work on the Form so
I am wondering if it a Report thing. I know I can do it on a report
because I have a report with it, but sometimes I can't get that method
to work on other reports.

Also, What do you mean by a split database? I am thinking I might be
missing out on something... ok I read a little and get the gist of
front end/back end. Can I make it so that I have multiple front ends
that can only see specific records based on certain criteria, such as,
let's say, the codedb.Properties("AppTitle")?? Is this how to have an
Access front end and a mySQL backend maybe... This is a goal of mine,
someday, when I have time to learn mySQL.

Thanks.

Probably best to have a table dedicated to keeping this kind of
information. Single row, one field for each item.

You can bind the table to a form if you want.

If you use a split database, you can put the config table in
the back end - and your title will tell you that you are
connected to the correct database.

You can use DLookup to get you title out of the table.
You can concatenate the whole DLookup into any
expression of property that you want - it is a function,
so you can use =Dlookup(...) in your form properties
if you want.

You can use a database property if you want. The database
title property is a good one to use. and you may want to use
it as well anyway. Go to Tools,Startup,
and fill in an Application Title. Then you can use it in
VBA and in form properties,
= codedb.Properties("AppTitle")

and it will also always be displayed in the application title bar.



I have a database that I have developed for multiple units to use. For
reasons that I won't go into, each unit must have an individual copy
of the database to store their data. BUT the databases need to be
identical. So far, annoying, but no big deal.

Anyway, what I am doing is creating the master copy where I get it all
correct and then I will make a copy for each unit and drop their data
into it... again no big deal. BUT in order to help keep them straight,
I would like to have specific labels that refer to the database
property Title bc the DB is on a network buried in a file more then 10
levels down so using the name is unrealistic.

I have used the Switchboard Manager to create my switchboard (I know,
don't shoot me, let's just move forward with that knowledge There
is currently a label at the top that says "Unit Tracking." I want it
to say "[DatabaseProperty.Title] Tracking" How would I go about
either:
1) making the Label Caption read that property or 2) after I change it
to a textbox, referring to it as part of the control source? I just
can't seem to find the right references for the second version.
CurrentDB.Name did give me the file name but again it was too long
because that gave me the whole path and I don't want to use the
filename anyway in case the users decide to change it. I can
individually go through all the labels. I was just hoping to do it
programatically if I could, espeically since unit names change often
enough to be a nuisance. If it were just the matter of the Switchboard
form I would do it manually but it actually is something that the
"powers that be" want on all the forms and there are quite a number.

Also in my Switchboard Items table, I would also like this same
information injected into the a couple of the ItemText labels. This is
less necessary though.

for example I have: SwitchboardID=1, ItemNumber=1, ItemText=Exit
Tracking Database, Command=6,Argument=(Null) but I want:
SwitchboardID=1, ItemNumber=1, ItemText=Exit
*DatabaseProperties.Title* Tracking Database,
Command=6,Argument=(Null).

Again this second issue is less necessary and I am less interested,
especially if the other process works.

Thanks in advance.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jp4]

Adding it to the load event of the form solved the form problem.

For the reports, I tried adding the expression to the query, but isn't
accepting it. I get the error message "Undefined function
'codedb.Properties' in expression."

Re: the mySQL comment and different "views", when I learn what that
means, we'll have another chat

 

[Jp4]

I am using Access 2000.

 

[Guest]

Sorry, I led you in the wrong direction there.

You can put the expression into a standard function,
in a standard module:

Function apptitle()
apptitle = CodeDb.Properties("apptitle")
End Function

Note: the CurrentDb.Properties("apptitle") automatically
appears on Access message boxes, but does not appear
on VBA message boxes (this is one of the things broken
in Access 2000, and never repaired). This means that
message boxes that come from Access.application, like macro
failures, automatically have your apptitle property, but message
boxes you create in VBA do not.

(david)

 

[Jp4]

Very cool. Thank you. One last question (I hope). How do I refer to it
as the field name for a table in a query?

Select [Tablename].[fieldname]

From [Tablename]

so I want something like...

Select [Tablename].["apptitle()"]

From [Tablename]

but that isn't working.

 

[Guest]

In Access SQL,

Select apptitle() as app From [Tablename];

In the Query By Example grid
app:apptitle()

Access does sometimes put [ ] square brackets around
things it does not recognise: you have to take them off again.

(david)

Very cool. Thank you. One last question (I hope). How do I refer to it
as the field name for a table in a query?

Select [Tablename].[fieldname]

From [Tablename]

so I want something like...

Select [Tablename].["apptitle()"]

From [Tablename]

but that isn't working.

Sorry, I led you in the wrong direction there.

You can put the expression into a standard function,
in a standard module:

Function apptitle()
apptitle = CodeDb.Properties("apptitle")
End Function

Note: the CurrentDb.Properties("apptitle") automatically
appears on Access message boxes, but does not appear
on VBA message boxes (this is one of the things broken
in Access 2000, and never repaired). This means that
message boxes that come from Access.application, like macro
failures, automatically have your apptitle property, but message
boxes you create in VBA do not.

(david)









- Show quoted text -

 

[Jp4]

Sorry I should have explained better. I need to call the apptitle() as
the fieldname to select criteria for my combobox.

My combobox query is:

SELECT fieldname1, fieldname2, fieldname3
FROM tablename
WHERE fieldname3=Yes;

I have multiple yes/no fields in the underlying table, where the
fieldname of one will match the apptitle. I want fieldname3 to change
based on the value of apptitle. Am I asking too much?

Thanks again for all your assistance with this.

In Access SQL,

Select apptitle() as app From [Tablename];

In the Query By Example grid
app:apptitle()

Access does sometimes put [ ] square brackets around
things it does not recognise: you have to take them off again.

(david)

Very cool. Thank you. One last question (I hope). How do I refer to it
as the field name for a table in a query?

Select [Tablename].[fieldname]

From [Tablename]

so I want something like...

Select [Tablename].["apptitle()"]

From [Tablename]

but that isn't working.

- Show quoted text -

 

[Guest]

You can't normally use a variable as a field name.

But if you already have a field with apptitle as fieldname,
you don't want to 'change' it do you?

And if it's bound to a combo box, you don't care what
the field name is?

And the select query does not have to be updateable?

So what you want is a complex criteria for selecting
the correct record?

Well, perhaps you have not designed your table correctly :~),
because the art and science of table design is to make criteria
like that simple.

Anyway, you have a bitmask, which allows records to match
multiple applications. You have spread the bitmask out across
multiple fields, which means that your criteria will need to be
spread out across multiple fields, or you can consolidate your
bit mask into one field.

method 1, query design grid:
bitA bitB bitC
apptitle="a"
or apptitle="c"
or apptitle="b"
or apptitle="a"
or apptitle="c"
or apptitle="d"

method 2
where appmask()= (-bitA + -2*bitB + -4*bitC)

Obviously, method 2 is much simpler if you are going to build
lots of queries like this, but you don't have to build lots of queries
like this: even if you have lots of queries, you could just join them
to one selection mask query built like method 1. But method 2
allows you to build the mask in VBA, where it is easier to get right.

If you want a variable field name, you use VBA and dynamic
SQL. If each record matches only one application, you need
to redesign your table and use a single "application index" field.

(david)

Sorry I should have explained better. I need to call the apptitle() as
the fieldname to select criteria for my combobox.

My combobox query is:

SELECT fieldname1, fieldname2, fieldname3
FROM tablename
WHERE fieldname3=Yes;

I have multiple yes/no fields in the underlying table, where the
fieldname of one will match the apptitle. I want fieldname3 to change
based on the value of apptitle. Am I asking too much?

Thanks again for all your assistance with this.

In Access SQL,

Select apptitle() as app From [Tablename];

In the Query By Example grid
app:apptitle()

Access does sometimes put [ ] square brackets around
things it does not recognise: you have to take them off again.

(david)

Very cool. Thank you. One last question (I hope). How do I refer to it
as the field name for a table in a query?

Select [Tablename].[fieldname]
From [Tablename]

so I want something like...

Select [Tablename].["apptitle()"]
From [Tablename]

but that isn't working.

Sorry, I led you in the wrong direction there.

You can put the expression into a standard function,
in a standard module:

Function apptitle()
apptitle = CodeDb.Properties("apptitle")
End Function

Note: the CurrentDb.Properties("apptitle") automatically
appears on Access message boxes, but does not appear
on VBA message boxes (this is one of the things broken
in Access 2000, and never repaired). This means that
message boxes that come from Access.application, like macro
failures, automatically have your apptitle property, but message
boxes you create in VBA do not.



I am using Access 2000.

Adding it to the load event of the form solved the form problem.

For the reports, I tried adding the expression to the query, but isn't
accepting it. I get the error message "Undefined function
'codedb.Properties' in expression."

Re: the mySQL comment and different "views", when I learn what that
means, we'll have another chat - Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jp4]

It is possible that I may have to change the design of my table, but
it is perfect for its original design. It is the fact that I am trying
to standardize multiple databases and want to have to manually change
as few criteria as possible. And I may just have to make this manual
change for each one or change the table setyp. (not so horrible, just
trying to avoid it as much as possible).

The reason is so that I can have the exact same database but for
different units. I have a "parent database" to standardize items
available for comboboxes in each of the unit databases. One of the
tables contains items that each unit has access to in their combo box,
but each unit has items that they never use and don't want to see in
the combobox.

So I added a field for each unit saying yes/no to that specific item,
where fieldname=unitname. Then the criteria for my combo box includes
a Where fieldname=yes (aka unitname=yes) statement, that allows only
the items that apply to that specific unit to be visable, which works
fine if I write the actual fieldname into the combobox query.

The problem is I was using the apptitle to identify which unit the
database belonged to and so I was trying to call that value for the
fieldname in this one specific query. I don't expect to do it in other
queries, but the easier it is, the better.

DBP ("Parent" db) --- DBC has a linked table to DBP
Table 1
Field1=item (text)
Field2=UnitnameA (y/n)
Field3=UnitnameB (y/n)
Field4=UnitnameC (y/n)
Field5=UnitnameD (y/n)
Field6=other (text)
Field7=other (y/n)
Field8=other
Field9=other
field10=other

DBC ("Child" db) with apptitle=Unitname(A or B or C or D)
Form1
Combobox
SELECT Field1, Field 6, Field(2-5) FROM Table 1 WHERE Field(2-5)=yes

OK lets point out here that it would just be better to have all the
info for all the units go into 1 single set of central tables and then
call the unit data out with the front end and maybe I have spent too
much time on this current issue and should spend more on convincing
people to change that part. You know... I think I am just going to do
this manually for now and then work on condensing it all.

One question so I can start mulling this over in the back of my head.
Is it better to use linked tables or queries to call data when working
with a front end?

You can't normally use a variable as a field name.

But if you already have a field with apptitle as fieldname,
you don't want to 'change' it do you?

And if it's bound to a combo box, you don't care what
the field name is?

And the select query does not have to be updateable?

So what you want is a complex criteria for selecting
the correct record?

Well, perhaps you have not designed your table correctly :~),
because the art and science of table design is to make criteria
like that simple.

Anyway, you have a bitmask, which allows records to match
multiple applications. You have spread the bitmask out across
multiple fields, which means that your criteria will need to be
spread out across multiple fields, or you can consolidate your
bit mask into one field.

method 1, query design grid:
bitA bitB bitC
apptitle="a"
or apptitle="c"
or apptitle="b"
or apptitle="a"
or apptitle="c"
or apptitle="d"

method 2
where appmask()= (-bitA + -2*bitB + -4*bitC)

Obviously, method 2 is much simpler if you are going to build
lots of queries like this, but you don't have to build lots of queries
like this: even if you have lots of queries, you could just join them
to one selection mask query built like method 1. But method 2
allows you to build the mask in VBA, where it is easier to get right.

If you want a variable field name, you use VBA and dynamic
SQL. If each record matches only one application, you need
to redesign your table and use a single "application index" field.

(david)

Sorry I should have explained better. I need to call the apptitle() as
the fieldname to select criteria for my combobox.

My combobox query is:

SELECT fieldname1, fieldname2, fieldname3
FROM tablename
WHERE fieldname3=Yes;

I have multiple yes/no fields in the underlying table, where the
fieldname of one will match the apptitle. I want fieldname3 to change
based on the value of apptitle. Am I asking too much?

Thanks again for all your assistance with this.

In Access SQL,
Select apptitle() as app From [Tablename];
In the Query By Example grid
app:apptitle()
Access does sometimes put [ ] square brackets around
things it does not recognise: you have to take them off again.
(david)

Very cool. Thank you. One last question (I hope). How do I refer to it
as the field name for a table in a query?
Select [Tablename].[fieldname]
From [Tablename]
so I want something like...
Select [Tablename].["apptitle()"]
From [Tablename]
but that isn't working.
Sorry, I led you in the wrong direction there.
You can put the expression into a standard function,
in a standard module:
Function apptitle()
apptitle = CodeDb.Properties("apptitle")
End Function
Note: the CurrentDb.Properties("apptitle") automatically
appears on Access message boxes, but does not appear
on VBA message boxes (this is one of the things broken
in Access 2000, and never repaired). This means that
message boxes that come from Access.application, like macro
failures, automatically have your apptitle property, but message
boxes you create in VBA do not.
(david)

I am using Access 2000.
Adding it to the load event of the form solved the form problem.
For the reports, I tried adding the expression to the query, but
isn't
accepting it. I get the error message "Undefined function
'codedb.Properties' in expression."
Re: the mySQL comment and different "views", when I learn what that
means, we'll have another chat - Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jp4]

But since this is a combobox query wouldn't I still have the same
problem even with the "front end concept"?

You can't normally use a variable as a field name.

But if you already have a field with apptitle as fieldname,
you don't want to 'change' it do you?

And if it's bound to a combo box, you don't care what
the field name is?

And the select query does not have to be updateable?

So what you want is a complex criteria for selecting
the correct record?

Well, perhaps you have not designed your table correctly :~),
because the art and science of table design is to make criteria
like that simple.

Anyway, you have a bitmask, which allows records to match
multiple applications. You have spread the bitmask out across
multiple fields, which means that your criteria will need to be
spread out across multiple fields, or you can consolidate your
bit mask into one field.

method 1, query design grid:
bitA bitB bitC
apptitle="a"
or apptitle="c"
or apptitle="b"
or apptitle="a"
or apptitle="c"
or apptitle="d"

method 2
where appmask()= (-bitA + -2*bitB + -4*bitC)

Obviously, method 2 is much simpler if you are going to build
lots of queries like this, but you don't have to build lots of queries
like this: even if you have lots of queries, you could just join them
to one selection mask query built like method 1. But method 2
allows you to build the mask in VBA, where it is easier to get right.

If you want a variable field name, you use VBA and dynamic
SQL. If each record matches only one application, you need
to redesign your table and use a single "application index" field.

(david)

Sorry I should have explained better. I need to call the apptitle() as
the fieldname to select criteria for my combobox.

My combobox query is:

SELECT fieldname1, fieldname2, fieldname3
FROM tablename
WHERE fieldname3=Yes;

I have multiple yes/no fields in the underlying table, where the
fieldname of one will match the apptitle. I want fieldname3 to change
based on the value of apptitle. Am I asking too much?

Thanks again for all your assistance with this.

In Access SQL,
Select apptitle() as app From [Tablename];
In the Query By Example grid
app:apptitle()
Access does sometimes put [ ] square brackets around
things it does not recognise: you have to take them off again.
(david)

Very cool. Thank you. One last question (I hope). How do I refer to it
as the field name for a table in a query?
Select [Tablename].[fieldname]
From [Tablename]
so I want something like...
Select [Tablename].["apptitle()"]
From [Tablename]
but that isn't working.
Sorry, I led you in the wrong direction there.
You can put the expression into a standard function,
in a standard module:
Function apptitle()
apptitle = CodeDb.Properties("apptitle")
End Function
Note: the CurrentDb.Properties("apptitle") automatically
appears on Access message boxes, but does not appear
on VBA message boxes (this is one of the things broken
in Access 2000, and never repaired). This means that
message boxes that come from Access.application, like macro
failures, automatically have your apptitle property, but message
boxes you create in VBA do not.
(david)

I am using Access 2000.
Adding it to the load event of the form solved the form problem.
For the reports, I tried adding the expression to the query, but
isn't
accepting it. I get the error message "Undefined function
'codedb.Properties' in expression."
Re: the mySQL comment and different "views", when I learn what that
means, we'll have another chat - Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jp4]

I figured it out. I did have to change my table structure.

I created an intersect table to house the yes/no data instead of using
what equated to a crosstab or pivot table type set-up. And then I
created a query for my combobox with apptitle() as the criteria.

Works like a charm. I need to work on how to create the necessary
associations for the intersect table, but for right now, it is no big
deal since I enter the data directly into the table anyway.

THANK YOU THANK YOU THANK YOU for all your help