expressions question and table variables - please help

[Earthling]

I want to enter an expression into my Default Value under my field
properties
within the design view of my table.

In English:

If the previous record has the same "product ID" number then by default
make the result FALSE, otherwise TRUE for the given field name value.

What this does is compare the previous Product ID (data type is Number) -
foreign key
to the Product Table ID (data type is AutoNumber) of the previous record.

Thus if the Product ID is the same number for the previous record "Product
Table ID minus 1"
of Product ID, then this means that our Product ID is not the first new
record entered.

Problem:

The Field Names are made up of multiple text so how can I enter an
expression
and also subtract 1 from the value the field name represents ?

The following didn't work:

If [Product Table ID -1] = [Product ID] Then False

Please help me write the expression for the default value.

Thanks,
Martians

 

[Dirk Goldgar]

I want to enter an expression into my Default Value under my field
properties
within the design view of my table.

In English:

If the previous record has the same "product ID" number then by
default make the result FALSE, otherwise TRUE for the given field
name value.

What this does is compare the previous Product ID (data type is
Number) - foreign key
to the Product Table ID (data type is AutoNumber) of the previous
record.

Thus if the Product ID is the same number for the previous record
"Product Table ID minus 1"
of Product ID, then this means that our Product ID is not the first
new record entered.

Problem:

The Field Names are made up of multiple text so how can I enter an
expression
and also subtract 1 from the value the field name represents ?

The following didn't work:

If [Product Table ID -1] = [Product ID] Then False

Please help me write the expression for the default value.

Thanks,
Martians

I do hope the Spirit and Opportunity rovers haven't been causing you any
inconvenience.

There are some problems with your statement of the problem, which we
need to clear up before I can suggest a solution. The first problem is
your use of the term "previous record". In a relational database such
as that managed by Access, there is no defined meaning to that term,
since records in a table are inherently unordered. That is to say,
"previous" only has meaning in a context in which the records have been
ordered by the sequencing operation of a query's ORDER BY clause or by
an index. So, what do you mean by "previous record" in this case?

Second, it's not really clear to me from your statement whether you are
interested in checking only the "previous record" -- whatever that means
in this context -- or in checking for *any* previous record that meets
some criteria. I also don't understand what it is you're checking
*for*; could you please restate it in different words?

I think it will be necessary for you to post the definitions of the
tables involved here, and their relationships. From the sound of it,
and though I don't believe I really understand what you're trying to
achieve, I don't think you can do it in the DefaultValue property of a
table field, but you may be able to do it on a form bound to that table.

 

[Earthling]

Hi,

What I am trying to do is get a checkbox to be selected by default in
a form that lists records of items, but infact I enter the initial data for
the items from a different form and press a button to have them
recorded via this original form.

The problem is that if I select the default value to be true for this
yes/no type field, then every single item that is added to my record
via the form(s) has TRUE selected in the checkbox. I only want true
to be selected by default only if its the inital data entry for the item.

In my tables I have an autonumber counter that adds a unique ID
number, and I have also a catagory ID number for each item in the
table.

Thus for each catagory number I only want to allow one checkbox
to be selected and not more. So I want to compare the unique ID
number's corresponding catagory ID number to the catagory ID
number in the previous (minus 1) unique ID number --- if the
catagory number is the same then our default checkbox property
becomes FALSE, otherwise TRUE.

I am looking for only one expression statement, I believe that this
method of comparing 2 sets of numbers in the table will solve the
problem although its not the best method. But I need to patch
some existing code and this is the easiest quick fix.

Thanks

PS: Martians and Earthlings are the same, I actually came back in
time from the year 2122, but live on Mars, originally human race
but we moved there as a species after 2066.

I want to enter an expression into my Default Value under my field
properties
within the design view of my table.

In English:

If the previous record has the same "product ID" number then by
default make the result FALSE, otherwise TRUE for the given field
name value.

What this does is compare the previous Product ID (data type is
Number) - foreign key
to the Product Table ID (data type is AutoNumber) of the previous
record.

Thus if the Product ID is the same number for the previous record
"Product Table ID minus 1"
of Product ID, then this means that our Product ID is not the first
new record entered.

Problem:

The Field Names are made up of multiple text so how can I enter an
expression
and also subtract 1 from the value the field name represents ?

The following didn't work:

If [Product Table ID -1] = [Product ID] Then False

Please help me write the expression for the default value.

Thanks,
Martians

I do hope the Spirit and Opportunity rovers haven't been causing you any
inconvenience.

There are some problems with your statement of the problem, which we
need to clear up before I can suggest a solution. The first problem is
your use of the term "previous record". In a relational database such
as that managed by Access, there is no defined meaning to that term,
since records in a table are inherently unordered. That is to say,
"previous" only has meaning in a context in which the records have been
ordered by the sequencing operation of a query's ORDER BY clause or by
an index. So, what do you mean by "previous record" in this case?

Second, it's not really clear to me from your statement whether you are
interested in checking only the "previous record" -- whatever that means
in this context -- or in checking for *any* previous record that meets
some criteria. I also don't understand what it is you're checking
*for*; could you please restate it in different words?

I think it will be necessary for you to post the definitions of the
tables involved here, and their relationships. From the sound of it,
and though I don't believe I really understand what you're trying to
achieve, I don't think you can do it in the DefaultValue property of a
table field, but you may be able to do it on a form bound to that table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tom Wickerath]

The problem is that if I select the default value to be true for

this yes/no type field, then every single item that is added to
my record via the form(s) has TRUE selected in the checkbox.
I only want true to be selected by default only if its the initial
data entry for the item.

A form has a Current event procedure that you might be able to use:

Private Sub Form_Current()

If Me.NewRecord Then
Me.checkboxname = True
End If

End Sub


Tom
_______________________________________


Hi,

What I am trying to do is get a checkbox to be selected by default in
a form that lists records of items, but infact I enter the initial data for
the items from a different form and press a button to have them
recorded via this original form.

The problem is that if I select the default value to be true for this
yes/no type field, then every single item that is added to my record
via the form(s) has TRUE selected in the checkbox. I only want true
to be selected by default only if its the inital data entry for the item.

In my tables I have an autonumber counter that adds a unique ID
number, and I have also a catagory ID number for each item in the
table.

Thus for each catagory number I only want to allow one checkbox
to be selected and not more. So I want to compare the unique ID
number's corresponding catagory ID number to the catagory ID
number in the previous (minus 1) unique ID number --- if the
catagory number is the same then our default checkbox property
becomes FALSE, otherwise TRUE.

I am looking for only one expression statement, I believe that this
method of comparing 2 sets of numbers in the table will solve the
problem although its not the best method. But I need to patch
some existing code and this is the easiest quick fix.

Thanks

PS: Martians and Earthlings are the same, I actually came back in
time from the year 2122, but live on Mars, originally human race
but we moved there as a species after 2066.

I want to enter an expression into my Default Value under my field
properties
within the design view of my table.

In English:

If the previous record has the same "product ID" number then by
default make the result FALSE, otherwise TRUE for the given field
name value.

What this does is compare the previous Product ID (data type is
Number) - foreign key
to the Product Table ID (data type is AutoNumber) of the previous
record.

Thus if the Product ID is the same number for the previous record
"Product Table ID minus 1"
of Product ID, then this means that our Product ID is not the first
new record entered.

Problem:

The Field Names are made up of multiple text so how can I enter an
expression
and also subtract 1 from the value the field name represents ?

The following didn't work:

If [Product Table ID -1] = [Product ID] Then False

Please help me write the expression for the default value.

Thanks,
Martians

I do hope the Spirit and Opportunity rovers haven't been causing you any
inconvenience.

There are some problems with your statement of the problem, which we
need to clear up before I can suggest a solution. The first problem is
your use of the term "previous record". In a relational database such
as that managed by Access, there is no defined meaning to that term,
since records in a table are inherently unordered. That is to say,
"previous" only has meaning in a context in which the records have been
ordered by the sequencing operation of a query's ORDER BY clause or by
an index. So, what do you mean by "previous record" in this case?

Second, it's not really clear to me from your statement whether you are
interested in checking only the "previous record" -- whatever that means
in this context -- or in checking for *any* previous record that meets
some criteria. I also don't understand what it is you're checking
*for*; could you please restate it in different words?

I think it will be necessary for you to post the definitions of the
tables involved here, and their relationships. From the sound of it,
and though I don't believe I really understand what you're trying to
achieve, I don't think you can do it in the DefaultValue property of a
table field, but you may be able to do it on a form bound to that table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi,

What I am trying to do is get a checkbox to be selected by default in
a form that lists records of items, but infact I enter the initial
data for the items from a different form and press a button to have
them
recorded via this original form.

The problem is that if I select the default value to be true for this
yes/no type field, then every single item that is added to my record
via the form(s) has TRUE selected in the checkbox. I only want true
to be selected by default only if its the inital data entry for the
item.

In my tables I have an autonumber counter that adds a unique ID
number, and I have also a catagory ID number for each item in the
table.

Thus for each catagory number I only want to allow one checkbox
to be selected and not more. So I want to compare the unique ID
number's corresponding catagory ID number to the catagory ID
number in the previous (minus 1) unique ID number --- if the
catagory number is the same then our default checkbox property
becomes FALSE, otherwise TRUE.

I am looking for only one expression statement, I believe that this
method of comparing 2 sets of numbers in the table will solve the
problem although its not the best method. But I need to patch
some existing code and this is the easiest quick fix.

Thanks

I'm quite sure I don't understand you completely, but I also wonder if
you've thought this through. You say you only want *one* record for a
particular CategoryID to have this boolean (yes/no) field set to True,
but then you say you only want to compare the CategoryID in the current
record to the one in a single record, the "previous" one whose ID is one
less than the ID of the current record. But that wouldn't guarantee
that only one record per CategoryID has the field set to True. What if
the "previous" record were for some other CategoryID? What if there
were some break in the sequential assignment of the unique ID numbers?
Note that if you are using an autonumber field for this unique ID you
will most certainly have gaps in the sequence.

Would it make more sense to set the check box to True if there is no
other record in the table with that CategoryID, and to False if there is
such a record? That would be easy to do, by using a DLookup expression
to see if such a record exists. You wouldn't be able to do it in the
control's DefaultValue property, because that will be evaluated for each
new record before you've actually entered the CategoryID, but you could
do it with code in the form's BeforeUpdate event or in the AfterUpdate
event of the CategoryID control.

Even taking this aproach, what happens if a record that originally had
this checkbox set to True should later be deleted? Now none of the
remaining records for that CategoryID has its checkbox set to True.
Depending on the significance of the checkbox, this could be a problem;
I really can't say.

What is the purpose of this "checkbox" field, may I ask? It seems to me
that all it can really do is tell you which is the first record entered
for a particular CategoryID. You could do that just as easily by having
a WhenCreated field (of type date/time) with its DefaultValue property
set to Now() in the table design. Then any time you want to identify
the first record for a given CategoryID, you just look for the one with
the minimum (earliest) WhenCreated. If you take this approach, you
don't actually need any code in the form, and the integrity of your data
is assured.

PS: Martians and Earthlings are the same, I actually came back in
time from the year 2122, but live on Mars, originally human race
but we moved there as a species after 2066.

Ah.

 

[Earthling]

Dirk,

Thanks - its really helpful to know that people like yourself make the
usenet
very "use" full. Your reply is very important and helpful, and a good omen.

****
I have thought about what you have said and all of it is wise.

The options of using the "DLookup" function somewhere
or
The options of using the "Date/Time" stampt for data entry.

I will try these out somehow with my very basic programming skills.

Martians