Combo Boxes

[Guest]

Good Morning

I have a bound form "Site Details" to table "Site Details", which has 2
Combo Boxes. In the first Combo the user selects the Client, if the user
selects client "a", then Combo Box 2 needs to only allow selection of
information from "Table1" and this needs to be limited to list, however if
any other client is selected then Combo Box 2 needs to effectively be a
standard text box where data is entered.
I have little knowledge of code so answers in words of one syllable please.
The second point to this question is that the table will be storing the site
addresses for all clients, for Client "a" I will need to populate the
remaining address fields in "Site Details" from "Table 1", however this will
mean that I am effectively storing the same data twice for client "a" in the
database, is this a good thing to be doing??

Many thanks in advance

Richard

 

[Guest]

No, it violates one of the basic rules of relational database normalization.
You should never store the same piece of data more than once.
What you are describing is a spreadsheet.
Here are some additional basic rules and ideas.
Never store a calculated value in a table if you store the information
necessary to calculate it. Most obvious example is a person's age. If you
store the birthday, you don't need to store the age. It will incorrect at
some point in time, anyway.
Naming conventions. This you need to pay attention to. You are already in
trouble.
You are naming things with the same name. When you say Site Details, which
is it, the table or the form.
Names should consist of letters, numbers, and the underscore character. Do
not use spaces or punctuation. Use a prefix naming convention that makes it
obvious what the object is. Never use any reserved words (Date, Name, Value,
Year, etc). This can confuse Access.
Your table should be tblSiteDetails
Your form should be frmSiteDetails
The combo to search for clients should be cboClient

Your concept of "if client a then use Table1 otherwise..." Is not sound.
You are venturing into "intelligent data", which is not a good idea. No
client should be handled differently than any other client.

Don't mean to beat you up. Just trying to get you off on the right foot.

If you can post back some more detail on what you want to do, I am sure we
can help you get started.

 

[Guest]

Ok no problem with the learning

Have moved on a little since the post on my own, but still not sure on right
track.
Here is where we are at now
Have to treat clients differently as I am able to access site address
details through another table within our organisation for client a, and the
powers that be wish for this table to be used for including the data into the
form Site Details and therefore into the table (of the same name for now)
Site Details, however this information will not be available for other
clients, but still have the need to record site address details for these
clients.
Hope this is relatively clear.
I am now at the stage where I have managed to get the second combo box to be
based upon inputs into the first combo box, using the following SQL statement
in the Record Source for the second combo box.

SELECT Table1.ID, Table1.[Organisation/Group], Table1.[Address 1],
Table1.[City/Town], Table1.Supplier FROM Table1 WHERE
(((Table1.Supplier)=Forms![Site Details]!Client)) ORDER BY Table1.ID DESC;

Got this example from the Microsoft Access website
I am trying to create a criteria that when the client "a" name is selected
in Combo box 1(know as Client) then we can see the appropriate data in Table1
and when any other client name is selected then nothing is shown to be
selected in Combo Box 2.
This works relatively ok, however it only works the first time data is
entered into the form following opening. If I continue and fill out all the
fields on the form and click the command button set to save the record and
create a new record, combo box 2 does not seem to refresh and no matter what
client is selected combo box 2 will only show what was shown from the first
entry. This is also the same if I change the entry into combo box 1 (client)
without saving the record.
I am thinking the form needs to refresh or some such thing.

Any thoughts

Thanks

Richard

 

[Guest]

Have now sorted the Requery problem by reading the article on Microsoft
Access website properly and putting in some code in Combo Box 1 AfterUpdate
event to requery .

Further question

Picking up on your comments earlier on Naming Conventions,
1 Would it be possible to take out the spaces in the fields and
table/query/form/report names without destroying all the links within the
database and
2 Also to rename the tables,queries, forms and reports to start with the
tbl, qry,frm, rpt again without having to go through every link.

ps I am using Access 2003

Thanks

Ok no problem with the learning

Have moved on a little since the post on my own, but still not sure on right
track.
Here is where we are at now
Have to treat clients differently as I am able to access site address
details through another table within our organisation for client a, and the
powers that be wish for this table to be used for including the data into the
form Site Details and therefore into the table (of the same name for now)
Site Details, however this information will not be available for other
clients, but still have the need to record site address details for these
clients.
Hope this is relatively clear.
I am now at the stage where I have managed to get the second combo box to be
based upon inputs into the first combo box, using the following SQL statement
in the Record Source for the second combo box.

SELECT Table1.ID, Table1.[Organisation/Group], Table1.[Address 1],
Table1.[City/Town], Table1.Supplier FROM Table1 WHERE
(((Table1.Supplier)=Forms![Site Details]!Client)) ORDER BY Table1.ID DESC;

Got this example from the Microsoft Access website
I am trying to create a criteria that when the client "a" name is selected
in Combo box 1(know as Client) then we can see the appropriate data in Table1
and when any other client name is selected then nothing is shown to be
selected in Combo Box 2.
This works relatively ok, however it only works the first time data is
entered into the form following opening. If I continue and fill out all the
fields on the form and click the command button set to save the record and
create a new record, combo box 2 does not seem to refresh and no matter what
client is selected combo box 2 will only show what was shown from the first
entry. This is also the same if I change the entry into combo box 1 (client)
without saving the record.
I am thinking the form needs to refresh or some such thing.

Any thoughts

Thanks

Richard

No, it violates one of the basic rules of relational database normalization.
You should never store the same piece of data more than once.
What you are describing is a spreadsheet.
Here are some additional basic rules and ideas.
Never store a calculated value in a table if you store the information
necessary to calculate it. Most obvious example is a person's age. If you
store the birthday, you don't need to store the age. It will incorrect at
some point in time, anyway.
Naming conventions. This you need to pay attention to. You are already in
trouble.
You are naming things with the same name. When you say Site Details, which
is it, the table or the form.
Names should consist of letters, numbers, and the underscore character. Do
not use spaces or punctuation. Use a prefix naming convention that makes it
obvious what the object is. Never use any reserved words (Date, Name, Value,
Year, etc). This can confuse Access.
Your table should be tblSiteDetails
Your form should be frmSiteDetails
The combo to search for clients should be cboClient

Your concept of "if client a then use Table1 otherwise..." Is not sound.
You are venturing into "intelligent data", which is not a good idea. No
client should be handled differently than any other client.

Don't mean to beat you up. Just trying to get you off on the right foot.

If you can post back some more detail on what you want to do, I am sure we
can help you get started.

 

[Guest]

Glad you are making progress. Sorry I have not been back sooner, but they
are expecting me to do some actual work. --- What a concept!

Anyway, here is a link to a utility that you can use to do the renaming.
There is a free version, but full verision is only $37.00 US and is the best
$37.00 I ever spent on software.

http://www.rickworld.com/download.html

--
Dave Hargis, Microsoft Access MVP

Have now sorted the Requery problem by reading the article on Microsoft
Access website properly and putting in some code in Combo Box 1 AfterUpdate
event to requery .

Further question

Picking up on your comments earlier on Naming Conventions,
1 Would it be possible to take out the spaces in the fields and
table/query/form/report names without destroying all the links within the
database and
2 Also to rename the tables,queries, forms and reports to start with the
tbl, qry,frm, rpt again without having to go through every link.

ps I am using Access 2003

Thanks

Ok no problem with the learning

Have moved on a little since the post on my own, but still not sure on right
track.
Here is where we are at now
Have to treat clients differently as I am able to access site address
details through another table within our organisation for client a, and the
powers that be wish for this table to be used for including the data into the
form Site Details and therefore into the table (of the same name for now)
Site Details, however this information will not be available for other
clients, but still have the need to record site address details for these
clients.
Hope this is relatively clear.
I am now at the stage where I have managed to get the second combo box to be
based upon inputs into the first combo box, using the following SQL statement
in the Record Source for the second combo box.

SELECT Table1.ID, Table1.[Organisation/Group], Table1.[Address 1],
Table1.[City/Town], Table1.Supplier FROM Table1 WHERE
(((Table1.Supplier)=Forms![Site Details]!Client)) ORDER BY Table1.ID DESC;

Got this example from the Microsoft Access website
I am trying to create a criteria that when the client "a" name is selected
in Combo box 1(know as Client) then we can see the appropriate data in Table1
and when any other client name is selected then nothing is shown to be
selected in Combo Box 2.
This works relatively ok, however it only works the first time data is
entered into the form following opening. If I continue and fill out all the
fields on the form and click the command button set to save the record and
create a new record, combo box 2 does not seem to refresh and no matter what
client is selected combo box 2 will only show what was shown from the first
entry. This is also the same if I change the entry into combo box 1 (client)
without saving the record.
I am thinking the form needs to refresh or some such thing.

Any thoughts

Thanks

Richard

No, it violates one of the basic rules of relational database normalization.
You should never store the same piece of data more than once.
What you are describing is a spreadsheet.
Here are some additional basic rules and ideas.
Never store a calculated value in a table if you store the information
necessary to calculate it. Most obvious example is a person's age. If you
store the birthday, you don't need to store the age. It will incorrect at
some point in time, anyway.
Naming conventions. This you need to pay attention to. You are already in
trouble.
You are naming things with the same name. When you say Site Details, which
is it, the table or the form.
Names should consist of letters, numbers, and the underscore character. Do
not use spaces or punctuation. Use a prefix naming convention that makes it
obvious what the object is. Never use any reserved words (Date, Name, Value,
Year, etc). This can confuse Access.
Your table should be tblSiteDetails
Your form should be frmSiteDetails
The combo to search for clients should be cboClient

Your concept of "if client a then use Table1 otherwise..." Is not sound.
You are venturing into "intelligent data", which is not a good idea. No
client should be handled differently than any other client.

Don't mean to beat you up. Just trying to get you off on the right foot.

If you can post back some more detail on what you want to do, I am sure we
can help you get started.

--
Dave Hargis, Microsoft Access MVP


:

Good Morning

I have a bound form "Site Details" to table "Site Details", which has 2
Combo Boxes. In the first Combo the user selects the Client, if the user
selects client "a", then Combo Box 2 needs to only allow selection of
information from "Table1" and this needs to be limited to list, however if
any other client is selected then Combo Box 2 needs to effectively be a
standard text box where data is entered.
I have little knowledge of code so answers in words of one syllable please.
The second point to this question is that the table will be storing the site
addresses for all clients, for Client "a" I will need to populate the
remaining address fields in "Site Details" from "Table 1", however this will
mean that I am effectively storing the same data twice for client "a" in the
database, is this a good thing to be doing??

Many thanks in advance

Richard

 

[Guest]

Am having some more probs with the Site Details form

If client a is selected in combo box 1 and the appropriate site ref number
entered into combo box 2 (both working now) I need to populate other address
fields within the form with the relevant information against the site ref.
I have created a query that picks the information based upon the selections
in Combo boxes 1 and 2 and am trying to use a macro with SetValue, but the
macro will not recognise the query, IIf statement below which fails and also
one below that works if I dont use the query ref.
As before I have done this as I have minimal knowledge of code.
Any help greatfully received

none working IIf statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),[Query]![SubClient]![Organisation/Group],Null)

working IIf Statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),5,Null)

Further question, does using macros rather than code increase the size of
the overall database??

Thanks

Richard
shame about actually needing to do some work, absolutely preposterous they
would ask you to do that

Glad you are making progress. Sorry I have not been back sooner, but they
are expecting me to do some actual work. --- What a concept!

Anyway, here is a link to a utility that you can use to do the renaming.
There is a free version, but full verision is only $37.00 US and is the best
$37.00 I ever spent on software.

http://www.rickworld.com/download.html

--
Dave Hargis, Microsoft Access MVP

Have now sorted the Requery problem by reading the article on Microsoft
Access website properly and putting in some code in Combo Box 1 AfterUpdate
event to requery .

Further question

Picking up on your comments earlier on Naming Conventions,
1 Would it be possible to take out the spaces in the fields and
table/query/form/report names without destroying all the links within the
database and
2 Also to rename the tables,queries, forms and reports to start with the
tbl, qry,frm, rpt again without having to go through every link.

ps I am using Access 2003

Thanks

Ok no problem with the learning

Have moved on a little since the post on my own, but still not sure on right
track.
Here is where we are at now
Have to treat clients differently as I am able to access site address
details through another table within our organisation for client a, and the
powers that be wish for this table to be used for including the data into the
form Site Details and therefore into the table (of the same name for now)
Site Details, however this information will not be available for other
clients, but still have the need to record site address details for these
clients.
Hope this is relatively clear.
I am now at the stage where I have managed to get the second combo box to be
based upon inputs into the first combo box, using the following SQL statement
in the Record Source for the second combo box.

SELECT Table1.ID, Table1.[Organisation/Group], Table1.[Address 1],
Table1.[City/Town], Table1.Supplier FROM Table1 WHERE
(((Table1.Supplier)=Forms![Site Details]!Client)) ORDER BY Table1.ID DESC;

Got this example from the Microsoft Access website
I am trying to create a criteria that when the client "a" name is selected
in Combo box 1(know as Client) then we can see the appropriate data in Table1
and when any other client name is selected then nothing is shown to be
selected in Combo Box 2.
This works relatively ok, however it only works the first time data is
entered into the form following opening. If I continue and fill out all the
fields on the form and click the command button set to save the record and
create a new record, combo box 2 does not seem to refresh and no matter what
client is selected combo box 2 will only show what was shown from the first
entry. This is also the same if I change the entry into combo box 1 (client)
without saving the record.
I am thinking the form needs to refresh or some such thing.

Any thoughts

Thanks

Richard
:

No, it violates one of the basic rules of relational database normalization.
You should never store the same piece of data more than once.
What you are describing is a spreadsheet.
Here are some additional basic rules and ideas.
Never store a calculated value in a table if you store the information
necessary to calculate it. Most obvious example is a person's age. If you
store the birthday, you don't need to store the age. It will incorrect at
some point in time, anyway.
Naming conventions. This you need to pay attention to. You are already in
trouble.
You are naming things with the same name. When you say Site Details, which
is it, the table or the form.
Names should consist of letters, numbers, and the underscore character. Do
not use spaces or punctuation. Use a prefix naming convention that makes it
obvious what the object is. Never use any reserved words (Date, Name, Value,
Year, etc). This can confuse Access.
Your table should be tblSiteDetails
Your form should be frmSiteDetails
The combo to search for clients should be cboClient

Your concept of "if client a then use Table1 otherwise..." Is not sound.
You are venturing into "intelligent data", which is not a good idea. No
client should be handled differently than any other client.

Don't mean to beat you up. Just trying to get you off on the right foot.

If you can post back some more detail on what you want to do, I am sure we
can help you get started.

--
Dave Hargis, Microsoft Access MVP


:

Good Morning

I have a bound form "Site Details" to table "Site Details", which has 2
Combo Boxes. In the first Combo the user selects the Client, if the user
selects client "a", then Combo Box 2 needs to only allow selection of
information from "Table1" and this needs to be limited to list, however if
any other client is selected then Combo Box 2 needs to effectively be a
standard text box where data is entered.
I have little knowledge of code so answers in words of one syllable please.
The second point to this question is that the table will be storing the site
addresses for all clients, for Client "a" I will need to populate the
remaining address fields in "Site Details" from "Table 1", however this will
mean that I am effectively storing the same data twice for client "a" in the
database, is this a good thing to be doing??

Many thanks in advance

Richard

 

[Guest]

Using macros does not really affect the size of the database any more than
any other object.
Macros are valuable for quick and simple tasks; however, as your experience
grows, you will find there are a lot of things that cannot be done with
Macros.

I think you will find that most professionals seldom use Macros, but prefer
to use VBA because you have more control over the application and because
Macros are very weak when it comes to error handling.

If you are trying to show the record for the value selected in your combo,
there is a pretty standard way to do this. Here is how I do it:

Private Sub cboFoobar_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[CLOWN_SHOES] = '" & Me.cboFoobar & "'"
If .NoMatch Then
MsgBox "The Clown Has No Shoes"
Else
Me.BookMark = .BookMark
End If
End With

End Sub

That is the basics. Sometimes it gets more complex, but 99% of the time you
will use this method. The breakdown:

Use the FindFirst method of the form's recordsetclone to look for the value
in the combo box. You tell it the name of the field, the operation, and what
to look for. In this case you reference the value in the combo box.
Then the NoMatch property will be True if it did not find the value and
False if it did.
If you find it, you set the form's bookmark to the recordsetclone's
Bookmark. This make the record where the value was found the current record
for the form.
Note that the FindFirst will stop on the first value it finds. If there are
duplicates, it doesn't know about them.

--
Dave Hargis, Microsoft Access MVP

Am having some more probs with the Site Details form

If client a is selected in combo box 1 and the appropriate site ref number
entered into combo box 2 (both working now) I need to populate other address
fields within the form with the relevant information against the site ref.
I have created a query that picks the information based upon the selections
in Combo boxes 1 and 2 and am trying to use a macro with SetValue, but the
macro will not recognise the query, IIf statement below which fails and also
one below that works if I dont use the query ref.
As before I have done this as I have minimal knowledge of code.
Any help greatfully received

none working IIf statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),[Query]![SubClient]![Organisation/Group],Null)

working IIf Statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),5,Null)

Further question, does using macros rather than code increase the size of
the overall database??

Thanks

Richard
shame about actually needing to do some work, absolutely preposterous they
would ask you to do that

Glad you are making progress. Sorry I have not been back sooner, but they
are expecting me to do some actual work. --- What a concept!

Anyway, here is a link to a utility that you can use to do the renaming.
There is a free version, but full verision is only $37.00 US and is the best
$37.00 I ever spent on software.

http://www.rickworld.com/download.html

--
Dave Hargis, Microsoft Access MVP

Have now sorted the Requery problem by reading the article on Microsoft
Access website properly and putting in some code in Combo Box 1 AfterUpdate
event to requery .

Further question

Picking up on your comments earlier on Naming Conventions,
1 Would it be possible to take out the spaces in the fields and
table/query/form/report names without destroying all the links within the
database and
2 Also to rename the tables,queries, forms and reports to start with the
tbl, qry,frm, rpt again without having to go through every link.

ps I am using Access 2003

Thanks

:

Ok no problem with the learning

Have moved on a little since the post on my own, but still not sure on right
track.
Here is where we are at now
Have to treat clients differently as I am able to access site address
details through another table within our organisation for client a, and the
powers that be wish for this table to be used for including the data into the
form Site Details and therefore into the table (of the same name for now)
Site Details, however this information will not be available for other
clients, but still have the need to record site address details for these
clients.
Hope this is relatively clear.
I am now at the stage where I have managed to get the second combo box to be
based upon inputs into the first combo box, using the following SQL statement
in the Record Source for the second combo box.

SELECT Table1.ID, Table1.[Organisation/Group], Table1.[Address 1],
Table1.[City/Town], Table1.Supplier FROM Table1 WHERE
(((Table1.Supplier)=Forms![Site Details]!Client)) ORDER BY Table1.ID DESC;

Got this example from the Microsoft Access website
I am trying to create a criteria that when the client "a" name is selected
in Combo box 1(know as Client) then we can see the appropriate data in Table1
and when any other client name is selected then nothing is shown to be
selected in Combo Box 2.
This works relatively ok, however it only works the first time data is
entered into the form following opening. If I continue and fill out all the
fields on the form and click the command button set to save the record and
create a new record, combo box 2 does not seem to refresh and no matter what
client is selected combo box 2 will only show what was shown from the first
entry. This is also the same if I change the entry into combo box 1 (client)
without saving the record.
I am thinking the form needs to refresh or some such thing.

Any thoughts

Thanks

Richard
:

No, it violates one of the basic rules of relational database normalization.
You should never store the same piece of data more than once.
What you are describing is a spreadsheet.
Here are some additional basic rules and ideas.
Never store a calculated value in a table if you store the information
necessary to calculate it. Most obvious example is a person's age. If you
store the birthday, you don't need to store the age. It will incorrect at
some point in time, anyway.
Naming conventions. This you need to pay attention to. You are already in
trouble.
You are naming things with the same name. When you say Site Details, which
is it, the table or the form.
Names should consist of letters, numbers, and the underscore character. Do
not use spaces or punctuation. Use a prefix naming convention that makes it
obvious what the object is. Never use any reserved words (Date, Name, Value,
Year, etc). This can confuse Access.
Your table should be tblSiteDetails
Your form should be frmSiteDetails
The combo to search for clients should be cboClient

Your concept of "if client a then use Table1 otherwise..." Is not sound.
You are venturing into "intelligent data", which is not a good idea. No
client should be handled differently than any other client.

Don't mean to beat you up. Just trying to get you off on the right foot.

If you can post back some more detail on what you want to do, I am sure we
can help you get started.

--
Dave Hargis, Microsoft Access MVP


:

Good Morning

I have a bound form "Site Details" to table "Site Details", which has 2
Combo Boxes. In the first Combo the user selects the Client, if the user
selects client "a", then Combo Box 2 needs to only allow selection of
information from "Table1" and this needs to be limited to list, however if
any other client is selected then Combo Box 2 needs to effectively be a
standard text box where data is entered.
I have little knowledge of code so answers in words of one syllable please.
The second point to this question is that the table will be storing the site
addresses for all clients, for Client "a" I will need to populate the
remaining address fields in "Site Details" from "Table 1", however this will
mean that I am effectively storing the same data twice for client "a" in the
database, is this a good thing to be doing??

Many thanks in advance

Richard

 

[Guest]

I am not sure the code you gave is what I want as there are no references to
the outside query that I can see.

The record I want to enter into the fields on the form based upon Combobox2
is selected by a query based upon the selection in combobox1. The query takes
information from table 1
Does this make sense

Using macros does not really affect the size of the database any more than
any other object.
Macros are valuable for quick and simple tasks; however, as your experience
grows, you will find there are a lot of things that cannot be done with
Macros.

I think you will find that most professionals seldom use Macros, but prefer
to use VBA because you have more control over the application and because
Macros are very weak when it comes to error handling.

If you are trying to show the record for the value selected in your combo,
there is a pretty standard way to do this. Here is how I do it:

Private Sub cboFoobar_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[CLOWN_SHOES] = '" & Me.cboFoobar & "'"
If .NoMatch Then
MsgBox "The Clown Has No Shoes"
Else
Me.BookMark = .BookMark
End If
End With

End Sub

That is the basics. Sometimes it gets more complex, but 99% of the time you
will use this method. The breakdown:

Use the FindFirst method of the form's recordsetclone to look for the value
in the combo box. You tell it the name of the field, the operation, and what
to look for. In this case you reference the value in the combo box.
Then the NoMatch property will be True if it did not find the value and
False if it did.
If you find it, you set the form's bookmark to the recordsetclone's
Bookmark. This make the record where the value was found the current record
for the form.
Note that the FindFirst will stop on the first value it finds. If there are
duplicates, it doesn't know about them.

--
Dave Hargis, Microsoft Access MVP

Am having some more probs with the Site Details form

If client a is selected in combo box 1 and the appropriate site ref number
entered into combo box 2 (both working now) I need to populate other address
fields within the form with the relevant information against the site ref.
I have created a query that picks the information based upon the selections
in Combo boxes 1 and 2 and am trying to use a macro with SetValue, but the
macro will not recognise the query, IIf statement below which fails and also
one below that works if I dont use the query ref.
As before I have done this as I have minimal knowledge of code.
Any help greatfully received

none working IIf statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),[Query]![SubClient]![Organisation/Group],Null)

working IIf Statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),5,Null)

Further question, does using macros rather than code increase the size of
the overall database??

Thanks

Richard
shame about actually needing to do some work, absolutely preposterous they
would ask you to do that

Glad you are making progress. Sorry I have not been back sooner, but they
are expecting me to do some actual work. --- What a concept!

Anyway, here is a link to a utility that you can use to do the renaming.
There is a free version, but full verision is only $37.00 US and is the best
$37.00 I ever spent on software.

http://www.rickworld.com/download.html

--
Dave Hargis, Microsoft Access MVP


:

Have now sorted the Requery problem by reading the article on Microsoft
Access website properly and putting in some code in Combo Box 1 AfterUpdate
event to requery .

Further question

Picking up on your comments earlier on Naming Conventions,
1 Would it be possible to take out the spaces in the fields and
table/query/form/report names without destroying all the links within the
database and
2 Also to rename the tables,queries, forms and reports to start with the
tbl, qry,frm, rpt again without having to go through every link.

ps I am using Access 2003

Thanks

:

Ok no problem with the learning

Have moved on a little since the post on my own, but still not sure on right
track.
Here is where we are at now
Have to treat clients differently as I am able to access site address
details through another table within our organisation for client a, and the
powers that be wish for this table to be used for including the data into the
form Site Details and therefore into the table (of the same name for now)
Site Details, however this information will not be available for other
clients, but still have the need to record site address details for these
clients.
Hope this is relatively clear.
I am now at the stage where I have managed to get the second combo box to be
based upon inputs into the first combo box, using the following SQL statement
in the Record Source for the second combo box.

SELECT Table1.ID, Table1.[Organisation/Group], Table1.[Address 1],
Table1.[City/Town], Table1.Supplier FROM Table1 WHERE
(((Table1.Supplier)=Forms![Site Details]!Client)) ORDER BY Table1.ID DESC;

Got this example from the Microsoft Access website
I am trying to create a criteria that when the client "a" name is selected
in Combo box 1(know as Client) then we can see the appropriate data in Table1
and when any other client name is selected then nothing is shown to be
selected in Combo Box 2.
This works relatively ok, however it only works the first time data is
entered into the form following opening. If I continue and fill out all the
fields on the form and click the command button set to save the record and
create a new record, combo box 2 does not seem to refresh and no matter what
client is selected combo box 2 will only show what was shown from the first
entry. This is also the same if I change the entry into combo box 1 (client)
without saving the record.
I am thinking the form needs to refresh or some such thing.

Any thoughts

Thanks

Richard
:

No, it violates one of the basic rules of relational database normalization.
You should never store the same piece of data more than once.
What you are describing is a spreadsheet.
Here are some additional basic rules and ideas.
Never store a calculated value in a table if you store the information
necessary to calculate it. Most obvious example is a person's age. If you
store the birthday, you don't need to store the age. It will incorrect at
some point in time, anyway.
Naming conventions. This you need to pay attention to. You are already in
trouble.
You are naming things with the same name. When you say Site Details, which
is it, the table or the form.
Names should consist of letters, numbers, and the underscore character. Do
not use spaces or punctuation. Use a prefix naming convention that makes it
obvious what the object is. Never use any reserved words (Date, Name, Value,
Year, etc). This can confuse Access.
Your table should be tblSiteDetails
Your form should be frmSiteDetails
The combo to search for clients should be cboClient

Your concept of "if client a then use Table1 otherwise..." Is not sound.
You are venturing into "intelligent data", which is not a good idea. No
client should be handled differently than any other client.

Don't mean to beat you up. Just trying to get you off on the right foot.

If you can post back some more detail on what you want to do, I am sure we
can help you get started.

--
Dave Hargis, Microsoft Access MVP


:

Good Morning

I have a bound form "Site Details" to table "Site Details", which has 2
Combo Boxes. In the first Combo the user selects the Client, if the user
selects client "a", then Combo Box 2 needs to only allow selection of
information from "Table1" and this needs to be limited to list, however if
any other client is selected then Combo Box 2 needs to effectively be a
standard text box where data is entered.
I have little knowledge of code so answers in words of one syllable please.
The second point to this question is that the table will be storing the site
addresses for all clients, for Client "a" I will need to populate the
remaining address fields in "Site Details" from "Table 1", however this will
mean that I am effectively storing the same data twice for client "a" in the
database, is this a good thing to be doing??

Many thanks in advance

Richard

 

[Guest]

The code I posted doesn't need a relation to the outside query. I don't
recall that you stated combo1 has an impact on what is selected on combo2.
--
Dave Hargis, Microsoft Access MVP

I am not sure the code you gave is what I want as there are no references to
the outside query that I can see.

The record I want to enter into the fields on the form based upon Combobox2
is selected by a query based upon the selection in combobox1. The query takes
information from table 1
Does this make sense

Using macros does not really affect the size of the database any more than
any other object.
Macros are valuable for quick and simple tasks; however, as your experience
grows, you will find there are a lot of things that cannot be done with
Macros.

I think you will find that most professionals seldom use Macros, but prefer
to use VBA because you have more control over the application and because
Macros are very weak when it comes to error handling.

If you are trying to show the record for the value selected in your combo,
there is a pretty standard way to do this. Here is how I do it:

Private Sub cboFoobar_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[CLOWN_SHOES] = '" & Me.cboFoobar & "'"
If .NoMatch Then
MsgBox "The Clown Has No Shoes"
Else
Me.BookMark = .BookMark
End If
End With

End Sub

That is the basics. Sometimes it gets more complex, but 99% of the time you
will use this method. The breakdown:

Use the FindFirst method of the form's recordsetclone to look for the value
in the combo box. You tell it the name of the field, the operation, and what
to look for. In this case you reference the value in the combo box.
Then the NoMatch property will be True if it did not find the value and
False if it did.
If you find it, you set the form's bookmark to the recordsetclone's
Bookmark. This make the record where the value was found the current record
for the form.
Note that the FindFirst will stop on the first value it finds. If there are
duplicates, it doesn't know about them.

--
Dave Hargis, Microsoft Access MVP

Am having some more probs with the Site Details form

If client a is selected in combo box 1 and the appropriate site ref number
entered into combo box 2 (both working now) I need to populate other address
fields within the form with the relevant information against the site ref.
I have created a query that picks the information based upon the selections
in Combo boxes 1 and 2 and am trying to use a macro with SetValue, but the
macro will not recognise the query, IIf statement below which fails and also
one below that works if I dont use the query ref.
As before I have done this as I have minimal knowledge of code.
Any help greatfully received

none working IIf statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),[Query]![SubClient]![Organisation/Group],Null)

working IIf Statement in macro

IIf(([Forms]![Site Details]![Client]="Nationwide Asbestos Property Surveys
Ltd"),5,Null)

Further question, does using macros rather than code increase the size of
the overall database??

Thanks

Richard
shame about actually needing to do some work, absolutely preposterous they
would ask you to do that

:

Glad you are making progress. Sorry I have not been back sooner, but they
are expecting me to do some actual work. --- What a concept!

Anyway, here is a link to a utility that you can use to do the renaming.
There is a free version, but full verision is only $37.00 US and is the best
$37.00 I ever spent on software.

http://www.rickworld.com/download.html

--
Dave Hargis, Microsoft Access MVP


:

Have now sorted the Requery problem by reading the article on Microsoft
Access website properly and putting in some code in Combo Box 1 AfterUpdate
event to requery .

Further question

Picking up on your comments earlier on Naming Conventions,
1 Would it be possible to take out the spaces in the fields and
table/query/form/report names without destroying all the links within the
database and
2 Also to rename the tables,queries, forms and reports to start with the
tbl, qry,frm, rpt again without having to go through every link.

ps I am using Access 2003

Thanks

:

Ok no problem with the learning

Have moved on a little since the post on my own, but still not sure on right
track.
Here is where we are at now
Have to treat clients differently as I am able to access site address
details through another table within our organisation for client a, and the
powers that be wish for this table to be used for including the data into the
form Site Details and therefore into the table (of the same name for now)
Site Details, however this information will not be available for other
clients, but still have the need to record site address details for these
clients.
Hope this is relatively clear.
I am now at the stage where I have managed to get the second combo box to be
based upon inputs into the first combo box, using the following SQL statement
in the Record Source for the second combo box.

SELECT Table1.ID, Table1.[Organisation/Group], Table1.[Address 1],
Table1.[City/Town], Table1.Supplier FROM Table1 WHERE
(((Table1.Supplier)=Forms![Site Details]!Client)) ORDER BY Table1.ID DESC;

Got this example from the Microsoft Access website
I am trying to create a criteria that when the client "a" name is selected
in Combo box 1(know as Client) then we can see the appropriate data in Table1
and when any other client name is selected then nothing is shown to be
selected in Combo Box 2.
This works relatively ok, however it only works the first time data is
entered into the form following opening. If I continue and fill out all the
fields on the form and click the command button set to save the record and
create a new record, combo box 2 does not seem to refresh and no matter what
client is selected combo box 2 will only show what was shown from the first
entry. This is also the same if I change the entry into combo box 1 (client)
without saving the record.
I am thinking the form needs to refresh or some such thing.

Any thoughts

Thanks

Richard
:

No, it violates one of the basic rules of relational database normalization.
You should never store the same piece of data more than once.
What you are describing is a spreadsheet.
Here are some additional basic rules and ideas.
Never store a calculated value in a table if you store the information
necessary to calculate it. Most obvious example is a person's age. If you
store the birthday, you don't need to store the age. It will incorrect at
some point in time, anyway.
Naming conventions. This you need to pay attention to. You are already in
trouble.
You are naming things with the same name. When you say Site Details, which
is it, the table or the form.
Names should consist of letters, numbers, and the underscore character. Do
not use spaces or punctuation. Use a prefix naming convention that makes it
obvious what the object is. Never use any reserved words (Date, Name, Value,
Year, etc). This can confuse Access.
Your table should be tblSiteDetails
Your form should be frmSiteDetails
The combo to search for clients should be cboClient

Your concept of "if client a then use Table1 otherwise..." Is not sound.
You are venturing into "intelligent data", which is not a good idea. No
client should be handled differently than any other client.

Don't mean to beat you up. Just trying to get you off on the right foot.

If you can post back some more detail on what you want to do, I am sure we
can help you get started.

--
Dave Hargis, Microsoft Access MVP


:

Good Morning

I have a bound form "Site Details" to table "Site Details", which has 2
Combo Boxes. In the first Combo the user selects the Client, if the user
selects client "a", then Combo Box 2 needs to only allow selection of
information from "Table1" and this needs to be limited to list, however if
any other client is selected then Combo Box 2 needs to effectively be a
standard text box where data is entered.
I have little knowledge of code so answers in words of one syllable please.
The second point to this question is that the table will be storing the site
addresses for all clients, for Client "a" I will need to populate the
remaining address fields in "Site Details" from "Table 1", however this will
mean that I am effectively storing the same data twice for client "a" in the
database, is this a good thing to be doing??

Many thanks in advance

Richard