DLookup Question, specifically about criteria

[Guest]

OK, my database form is based off of a table titled: Show Events, and I want
to pull separate data from a foreign table titled: Show Stores.

One of the data fields I am trying to pull over from the foreign table is
the year that the store joined the organization, which is the field titled:
MemberSince. I am using the expression equation in an unbound control:

=DLookUp("[MemberSince]","Show Stores","[Count]=1")

Count is the field on the foreign table that gives a unique ID number to
each store.

My problem is that when I scroll through the different pages on the form, it
shows the same year for the store that is given a Count ID number of 1. Is
there a way to change the expression so that when I scroll through from store
count 1 through 100 that it gives me 100 different years according to the
unique company, as opposed to giving me the Year that Store 1 (Count = 1)
joined?

Hopefully this makes sense! Thanks!

 

[strive4peace]

Hi Cameron,

put the equation in the RecordSource of the form instead of making it a
calculated field on the form

MemSince: nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

then, use MemSince as the ControlSource for the control on the form

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Well, I am placing the equation in the "Control Source" under properties of
the control. I go into the expression builder under the Data tab of the
properties menu for the unbound control.

So, when you say place the equation in the Record Source...where is that?

Hi Cameron,

put the equation in the RecordSource of the form instead of making it a
calculated field on the form

MemSince: nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

then, use MemSince as the ControlSource for the control on the form

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

OK, my database form is based off of a table titled: Show Events, and I want
to pull separate data from a foreign table titled: Show Stores.

One of the data fields I am trying to pull over from the foreign table is
the year that the store joined the organization, which is the field titled:
MemberSince. I am using the expression equation in an unbound control:

=DLookUp("[MemberSince]","Show Stores","[Count]=1")

Count is the field on the foreign table that gives a unique ID number to
each store.

My problem is that when I scroll through the different pages on the form, it
shows the same year for the store that is given a Count ID number of 1. Is
there a way to change the expression so that when I scroll through from store
count 1 through 100 that it gives me 100 different years according to the
unique company, as opposed to giving me the Year that Store 1 (Count = 1)
joined?

Hopefully this makes sense! Thanks!

 

[Guest]

And keep in mind that I want to keep the current table, Show Events, as the
main source for data on the form. There is just one section that I am
wanting to pull a few fields from the foreign table, Show Stores. Hopefully
that makes sense.

 

[strive4peace]

add field to RecordSource
---


Hi Cameron,

RecordSource means for the form or report -- in other words, in the
underlying query

turn on the Properties window

click in the upper left where the rulers intersect to select the form or
report

in the Properties window, click on the Data tab

click in the RecordSource property and then click tht builder button
]...] off to the right

add the calculated column there

field --> MemSince: nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Well, I am placing the equation in the "Control Source" under properties of
the control. I go into the expression builder under the Data tab of the
properties menu for the unbound control.

So, when you say place the equation in the Record Source...where is that?

Hi Cameron,

put the equation in the RecordSource of the form instead of making it a
calculated field on the form

MemSince: nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

then, use MemSince as the ControlSource for the control on the form

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

OK, my database form is based off of a table titled: Show Events, and I want
to pull separate data from a foreign table titled: Show Stores.

One of the data fields I am trying to pull over from the foreign table is
the year that the store joined the organization, which is the field titled:
MemberSince. I am using the expression equation in an unbound control:

=DLookUp("[MemberSince]","Show Stores","[Count]=1")

Count is the field on the foreign table that gives a unique ID number to
each store.

My problem is that when I scroll through the different pages on the form, it
shows the same year for the store that is given a Count ID number of 1. Is
there a way to change the expression so that when I scroll through from store
count 1 through 100 that it gives me 100 different years according to the
unique company, as opposed to giving me the Year that Store 1 (Count = 1)
joined?

Hopefully this makes sense! Thanks!

 

[strive4peace]

Hi Cameron,

gave instruction for adding to RecordSource in other post ...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Student]

Well, I am placing the equation in the "Control Source" under properties
of
the control. I go into the expression builder under the Data tab of the
properties menu for the unbound control.

So, when you say place the equation in the Record Source...where is that?

Hi Cameron,

put the equation in the RecordSource of the form instead of making it a
calculated field on the form

MemSince: nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

then, use MemSince as the ControlSource for the control on the form

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

OK, my database form is based off of a table titled: Show Events, and I
want
to pull separate data from a foreign table titled: Show Stores.

One of the data fields I am trying to pull over from the foreign table
is
the year that the store joined the organization, which is the field
titled:
MemberSince. I am using the expression equation in an unbound control:

=DLookUp("[MemberSince]","Show Stores","[Count]=1")

Count is the field on the foreign table that gives a unique ID number
to
each store.

My problem is that when I scroll through the different pages on the
form, it
shows the same year for the store that is given a Count ID number of 1.
Is
there a way to change the expression so that when I scroll through from
store
count 1 through 100 that it gives me 100 different years according to
the
unique company, as opposed to giving me the Year that Store 1 (Count =
1)
joined?

Hopefully this makes sense! Thanks!

 

[Guest]

OK, now when I try that, I have added the table "Show Stores" to the SQL
statement query builder. Then I choose "MemberSince" for the Field name,
then I add your equation. So it looks exactly like this in the Field box:

MemberSince:nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

Then I choose Show Stores as the Table and click Show. Next I go back to
design view, and went to the control that I want to show this data and added
MemberSince as its Control Source.

When I then go to the other view to see if it works it asks me for

When I go to check if it works, it asks me Enter a Parameter Value, and if
it eventually goes away after I mess with it, it shows the #Name? error on
everything...any ideas?

Something I thought about doing was adding the Show Stores table data to the
main "Show Events" table, this would make things a lot easier, and it
wouldn't mess anything up in the database. What do you think? Thanks for
all your help!

 

[Douglas J. Steele]

Any time you have embedded spaces in a name (such as Show Stores), you must
use square brackets around it:

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

Far better not to use spaces in names!

 

[Guest]

I added the brackets, and still no change...I still have the #Name? error in
all controls, and before I can even get to see the controls, it asks me three
times for parameter values: events. My form has three tabs, and the info I
am trying to pull from the foreign table is on the third tab. When I remove
the expression equation from teh propperties of the form, it goes back to
normal. I can then see all the data from the mani table that I want on my
form. My trouble is still pulling that data from the foreign table Show
Stores, and beign able to see all data without a #Name? error. Would it be a
bad idea to throw the data from the small foreign table, Show Stores, into my
main table Show Events? If I did that, the data would probably show up
without an issue...right?

Any time you have embedded spaces in a name (such as Show Stores), you must
use square brackets around it:

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

Far better not to use spaces in names!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, now when I try that, I have added the table "Show Stores" to the SQL
statement query builder. Then I choose "MemberSince" for the Field name,
then I add your equation. So it looks exactly like this in the Field box:

MemberSince:nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

Then I choose Show Stores as the Table and click Show. Next I go back to
design view, and went to the control that I want to show this data and
added
MemberSince as its Control Source.

When I then go to the other view to see if it works it asks me for

When I go to check if it works, it asks me Enter a Parameter Value, and if
it eventually goes away after I mess with it, it shows the #Name? error on
everything...any ideas?

Something I thought about doing was adding the Show Stores table data to
the
main "Show Events" table, this would make things a lot easier, and it
wouldn't mess anything up in the database. What do you think? Thanks for
all your help!

 

[Douglas J. Steele]

Sorry, you're saying when you remove

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

from the query, you no longer get the prompts opening the form? That makes
no sense (unless Show Stores is a query, not a table)

What happens if you run the query by itself, as opposed to opening the form?

For what it's worth, you're not pulling data from a form: forms don't have
data, tables do.

Changing your Show Events table only makes sense if the data belongs in that
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I added the brackets, and still no change...I still have the #Name? error
in
all controls, and before I can even get to see the controls, it asks me
three
times for parameter values: events. My form has three tabs, and the info
I
am trying to pull from the foreign table is on the third tab. When I
remove
the expression equation from teh propperties of the form, it goes back to
normal. I can then see all the data from the mani table that I want on my
form. My trouble is still pulling that data from the foreign table Show
Stores, and beign able to see all data without a #Name? error. Would it
be a
bad idea to throw the data from the small foreign table, Show Stores, into
my
main table Show Events? If I did that, the data would probably show up
without an issue...right?

Any time you have embedded spaces in a name (such as Show Stores), you
must
use square brackets around it:

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

Far better not to use spaces in names!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, now when I try that, I have added the table "Show Stores" to the
SQL
statement query builder. Then I choose "MemberSince" for the Field
name,
then I add your equation. So it looks exactly like this in the Field
box:

MemberSince:nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

Then I choose Show Stores as the Table and click Show. Next I go back
to
design view, and went to the control that I want to show this data and
added
MemberSince as its Control Source.

When I then go to the other view to see if it works it asks me for

When I go to check if it works, it asks me Enter a Parameter Value, and
if
it eventually goes away after I mess with it, it shows the #Name? error
on
everything...any ideas?

Something I thought about doing was adding the Show Stores table data
to
the
main "Show Events" table, this would make things a lot easier, and it
wouldn't mess anything up in the database. What do you think? Thanks
for
all your help!

 

[Guest]

Yes, when I make the form back to its original form (without the MemberSince
expression equation), it does not prompt me for anything. Heh, if I knew
why, I wouldn't be posting on here. But, when I leave things as they are,
and run the query with the MemberSince equation in the field box, it brings
up the year the Count = 1 Member joined the organization. Unfortunately, I
need it to bring up the 101 unique years, for all 101 members. Any ideas?
Sorry for any inconvenience! Thanks.

Sorry, you're saying when you remove

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

from the query, you no longer get the prompts opening the form? That makes
no sense (unless Show Stores is a query, not a table)

What happens if you run the query by itself, as opposed to opening the form?

For what it's worth, you're not pulling data from a form: forms don't have
data, tables do.

Changing your Show Events table only makes sense if the data belongs in that
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I added the brackets, and still no change...I still have the #Name? error
in
all controls, and before I can even get to see the controls, it asks me
three
times for parameter values: events. My form has three tabs, and the info
I
am trying to pull from the foreign table is on the third tab. When I
remove
the expression equation from teh propperties of the form, it goes back to
normal. I can then see all the data from the mani table that I want on my
form. My trouble is still pulling that data from the foreign table Show
Stores, and beign able to see all data without a #Name? error. Would it
be a
bad idea to throw the data from the small foreign table, Show Stores, into
my
main table Show Events? If I did that, the data would probably show up
without an issue...right?

Any time you have embedded spaces in a name (such as Show Stores), you
must
use square brackets around it:

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

Far better not to use spaces in names!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OK, now when I try that, I have added the table "Show Stores" to the
SQL
statement query builder. Then I choose "MemberSince" for the Field
name,
then I add your equation. So it looks exactly like this in the Field
box:

MemberSince:nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

Then I choose Show Stores as the Table and click Show. Next I go back
to
design view, and went to the control that I want to show this data and
added
MemberSince as its Control Source.

When I then go to the other view to see if it works it asks me for

When I go to check if it works, it asks me Enter a Parameter Value, and
if
it eventually goes away after I mess with it, it shows the #Name? error
on
everything...any ideas?

Something I thought about doing was adding the Show Stores table data
to
the
main "Show Events" table, this would make things a lot easier, and it
wouldn't mess anything up in the database. What do you think? Thanks
for
all your help!

 

[Guest]

Another thing I noticed that might help you help me (lol)...is when I close
out of the expression builder, the Recrod Source says this:

SELECT nz(DLookUp("[MemberSince]","Show Stores","[Count]=1")) AS MemberSince
FROM [Show Events] INNER JOIN [Show Stores] ON [Show Events].Store = [Show
Stores].Store;

I am curious if there is a way to get the query and the control on my form
to scroll through the 101 different years according to the current store I am
looking at on the form.

 

[Douglas J. Steele]

I'm not sure I understand exactly what you're trying to do, but can you join
the two tables into a single query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, when I make the form back to its original form (without the
MemberSince
expression equation), it does not prompt me for anything. Heh, if I knew
why, I wouldn't be posting on here. But, when I leave things as they are,
and run the query with the MemberSince equation in the field box, it
brings
up the year the Count = 1 Member joined the organization. Unfortunately,
I
need it to bring up the 101 unique years, for all 101 members. Any ideas?
Sorry for any inconvenience! Thanks.

Sorry, you're saying when you remove

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

from the query, you no longer get the prompts opening the form? That
makes
no sense (unless Show Stores is a query, not a table)

What happens if you run the query by itself, as opposed to opening the
form?

For what it's worth, you're not pulling data from a form: forms don't
have
data, tables do.

Changing your Show Events table only makes sense if the data belongs in
that
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I added the brackets, and still no change...I still have the #Name?
error
in
all controls, and before I can even get to see the controls, it asks me
three
times for parameter values: events. My form has three tabs, and the
info
I
am trying to pull from the foreign table is on the third tab. When I
remove
the expression equation from teh propperties of the form, it goes back
to
normal. I can then see all the data from the mani table that I want on
my
form. My trouble is still pulling that data from the foreign table
Show
Stores, and beign able to see all data without a #Name? error. Would
it
be a
bad idea to throw the data from the small foreign table, Show Stores,
into
my
main table Show Events? If I did that, the data would probably show up
without an issue...right?

:

Any time you have embedded spaces in a name (such as Show Stores), you
must
use square brackets around it:

MemberSince:nz(DLookUp("[MemberSince]","[Show Stores]","[Count]=1"))

Far better not to use spaces in names!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OK, now when I try that, I have added the table "Show Stores" to the
SQL
statement query builder. Then I choose "MemberSince" for the Field
name,
then I add your equation. So it looks exactly like this in the
Field
box:

MemberSince:nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

Then I choose Show Stores as the Table and click Show. Next I go
back
to
design view, and went to the control that I want to show this data
and
added
MemberSince as its Control Source.

When I then go to the other view to see if it works it asks me for

When I go to check if it works, it asks me Enter a Parameter Value,
and
if
it eventually goes away after I mess with it, it shows the #Name?
error
on
everything...any ideas?

Something I thought about doing was adding the Show Stores table
data
to
the
main "Show Events" table, this would make things a lot easier, and
it
wouldn't mess anything up in the database. What do you think?
Thanks
for
all your help!

 

[strive4peace]

Hi Cameron,

You can add and should to the criteria for the equation to limit it to
particular criteria

Where is the SOURCE for MemberSince? Is that field in a table you are
already using for the source of your form?

If [Show Stores] is a query, where does its information come from?
specifically, MemberSince -- and in the table where that comes from,
what is the primary key?

As Doug said, since you use a space in [Show Stores], you always need to
put brackets around it when you use it in an equation or in code

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hmm I'll try to explain the whole thing:

OK I have a form titled Show Events, and it brings up data that involves
people who are attending Shows (as in jewelery shows where vendors sell to
jewelers). The form's main table source is the table titled "Show Events."

At the top of the Detail section is a combo box control that is titled
Store...I can click the down arrow on that control and I can choose from a
list of any jewelery store that is attending the show.

Below this is 3 tabs. One titled Room Info, where it lists numerous things
about who is staying in what room, how they pay, what the costs are, etc.
This tab also pulls from the main source for the form, table: "Show Events."
The second tab has a subform on it that describes which seminars and events
the attendees fromt he stores are going to. This info is pulled from query
titled "Show Seminars."

The third tab is where I come in. Everything I have mentioned above was
done for the company I work for by a marketing firm, which we have recently
stopped working with. The people that use this form have asked me to adjust
this form because of time constraints in their work schedule. They have to
alternate back and forth between the form I have described and another form
titled "Show Stores."

So, they asked me if I could combine the two, and the best way I thought of
doing this is adding a third tab to the "Show Events" form and copying over
the controls.

The "Show Stores" form has five controls and a subform. The first control
is the same list box that allows me to choose from any store that attends the
Show. The next four controls are: Fax, Date (which I am currently trying to
find out if it is even needed), MemberSince (the year the store joined the
organization I work for), and a check box titled NewMember (this is checked
if the store is a new member to the org. in the current year).

I successfully copied over the subform from the "Show Stores" form to the
third tab on the "Show Events" form, and when I move through the combo box at
the top of the details section, the subform correctly shows the attendees
according to the store I have selected. My problem has been in copying the
other controls. Obviously the Store list box from the other form isn't
needed, as the "Show Events" form already has one, but when I tried copying
over the other controls, they didn't work.

I has forgotten in my limited use of Access that the form had a Record
Source. So, my issue now is how to create those controls on the third tab of
the "Show Events" form. The info that I want in those controls is from a
table titled "Show Stores." And if you recall, the main source for the "Show
Events" form is "Show Events" table.

My end goal is to put those controls on that third tab, and for those
controls to list the correct data according to the store I have selected from
the combo box at the top of the details section, right above the three tabs.

Long, but hopefully it helps! THANKS A TON!

 

[Guest]

Hmm I'll try to explain the whole thing:

OK I have a form titled Show Events, and it brings up data that involves
people who are attending Shows (as in jewelery shows where vendors sell to
jewelers). The form's main table source is the table titled "Show Events."

At the top of the Detail section is a combo box control that is titled
Store...I can click the down arrow on that control and I can choose from a
list of any jewelery store that is attending the show.

Below this is 3 tabs. One titled Room Info, where it lists numerous things
about who is staying in what room, how they pay, what the costs are, etc.
This tab also pulls from the main source for the form, table: "Show Events."
The second tab has a subform on it that describes which seminars and events
the attendees fromt he stores are going to. This info is pulled from query
titled "Show Seminars."

The third tab is where I come in. Everything I have mentioned above was
done for the company I work for by a marketing firm, which we have recently
stopped working with. The people that use this form have asked me to adjust
this form because of time constraints in their work schedule. They have to
alternate back and forth between the form I have described and another form
titled "Show Stores."

So, they asked me if I could combine the two, and the best way I thought of
doing this is adding a third tab to the "Show Events" form and copying over
the controls.

The "Show Stores" form has five controls and a subform. The first control
is the same list box that allows me to choose from any store that attends the
Show. The next four controls are: Fax, Date (which I am currently trying to
find out if it is even needed), MemberSince (the year the store joined the
organization I work for), and a check box titled NewMember (this is checked
if the store is a new member to the org. in the current year).

I successfully copied over the subform from the "Show Stores" form to the
third tab on the "Show Events" form, and when I move through the combo box at
the top of the details section, the subform correctly shows the attendees
according to the store I have selected. My problem has been in copying the
other controls. Obviously the Store list box from the other form isn't
needed, as the "Show Events" form already has one, but when I tried copying
over the other controls, they didn't work.

I has forgotten in my limited use of Access that the form had a Record
Source. So, my issue now is how to create those controls on the third tab of
the "Show Events" form. The info that I want in those controls is from a
table titled "Show Stores." And if you recall, the main source for the "Show
Events" form is "Show Events" table.

My end goal is to put those controls on that third tab, and for those
controls to list the correct data according to the store I have selected from
the combo box at the top of the details section, right above the three tabs.

Long, but hopefully it helps! THANKS A TON!

 

[strive4peace]

ShowFields
---


Hi Cameron,

thanks for all the information!

at this point, we need to know your data structure. Here is something
you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Sorry, I am a little confused as to where to start doing that stuff. Can
you clarify it a little or something? Thanks, and sorry!

 

[strive4peace]

Create General Module, Reference DAO Library, Run Code
---


Hi Cameron,

Sure

*** How to Create a General Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste <smile>) the code in

for this code, make sure you have a reference to a Microsoft DAO Library

once the code is in the module sheet, from the menu -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

then, save. You will be prompted for a module name. Call it -->
mod_ShowFields

the code you will want to paste in starts with

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library

and goes down to the last -->

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

any line starting with a single quote ' is a comment and it will be green

as the instructions say, you will need to reference a Microsoft DAO
library (it could be selected already but you will have to look). do
this BEFORE you try to compile the code


*** DAO Library Reference ***

from the menu -->
Tools, References... from a module window

if there is not a DAO Library checked near the top of the list, scroll
to Microsoft DAO 3.6 Object Library and check it

~~~~~~~~~~~~~~~~~~~~``
once you have done this...

click in the *RunShowFieldsForAllTables* sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

~~~~

don't be scared by a little VBA code It is not tough to learn
programming -- and if this is something you may be interested in,
download and read this:

Access Basics
http://allenbrowne.com/tips.html
Tips for Casual Users
Access Basics: free tutorial - Word document by Crystal (Access MVP)

30-page Word document on Access Basics (for Programming) -- it doesn't
cover VBA, but prepares you for it because it covers essentials in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*