How to return a certain record from a table

[gmazza via AccessMonster.com]

Hi there,
I have a table with 3 columns: TrialId, SequenceNo, ValueNo

Here is some sample data:

TrialId SequenceNo ValueNo
1 1 0
1 1 1
1 1 2
1 2 0
1 2 1

When inserting a new record for the current trial and sequence, I need the
ValueNo to increase by 1.
So if I insert a new value for TrialId 1 and SequenceNo 1, I want the ValueNo
to be 3.
I tried doing a DLookup for this but I can't get it to goto the last record
of TrialId 1 and SequenceNo 1 and adding 1 to the ValueNo.
Is there an easier way?
Thanks!

 

[Dirk Goldgar]

Hi there,
I have a table with 3 columns: TrialId, SequenceNo, ValueNo

Here is some sample data:

TrialId SequenceNo ValueNo
1 1 0
1 1 1
1 1 2
1 2 0
1 2 1

When inserting a new record for the current trial and sequence, I need the
ValueNo to increase by 1.
So if I insert a new value for TrialId 1 and SequenceNo 1, I want the
ValueNo
to be 3.
I tried doing a DLookup for this but I can't get it to goto the last
record
of TrialId 1 and SequenceNo 1 and adding 1 to the ValueNo.

You could use DMax for this. How about this:

Dim lngTrialID As Long
Dim lngSequenceNo As Long
Dim lngNextValueNo As Long

lngTriaID = 1 ' or whatever
lngSequenceNo = 1 ' or whatever

lngNextValueNo = 1 + _
Nz(DMax("ValueNo", "YourTableName", _
"TrialID=" & lngTrialID & _
" AND SequenceNo=" & lngSequenceNo & "), 0)

You need to change "YourTableName" above to the name of your table.

 

[gmazza via AccessMonster.com]

Thanks for your reply!
I was getting an error on this line:
" AND SequenceNo=" & lngSequenceNo & "), 0)

so I took out the quotes and & to look like this:
" AND SequenceNo=" & lngSequenceNo), 0)

Not sure if thats right but it didn't error. However, it isn't working, it is
still grabbing the 0 and adding 1 to it. Same for any new records after that,
keeps adding 1 to 0.

Hi there,
I have a table with 3 columns: TrialId, SequenceNo, ValueNo

[quoted text clipped - 16 lines]

record
of TrialId 1 and SequenceNo 1 and adding 1 to the ValueNo.

You could use DMax for this. How about this:

Dim lngTrialID As Long
Dim lngSequenceNo As Long
Dim lngNextValueNo As Long

lngTriaID = 1 ' or whatever
lngSequenceNo = 1 ' or whatever

lngNextValueNo = 1 + _
Nz(DMax("ValueNo", "YourTableName", _
"TrialID=" & lngTrialID & _
" AND SequenceNo=" & lngSequenceNo & "), 0)

You need to change "YourTableName" above to the name of your table.

 

[Dirk Goldgar]

Thanks for your reply!
I was getting an error on this line:
" AND SequenceNo=" & lngSequenceNo & "), 0)

so I took out the quotes and & to look like this:
" AND SequenceNo=" & lngSequenceNo), 0)

Not sure if thats right but it didn't error. However, it isn't working, it
is
still grabbing the 0 and adding 1 to it. Same for any new records after
that,
keeps adding 1 to 0.

Hmm, you're right that I had a typo there, and I see another minor glitch,
but your corrected form should not have given you 1 every time. This
revised version works for me, with the example data you posted:

lngNextValueNo = 1 + _
Nz(DMax("ValueNo", "TrialData", _
"TrialID=" & lngTrialID & _
" AND SequenceNo=" & lngSequenceNo), -1)


Note that, in the above, I used "TrialData" as the name of the table. You
must change that to the name of your table.

Please let me know if the above, modified appropriately, doesn't work for
you. Where are you getting the values of lngTrialID and lngSequenceNo? I
hard-coded them for the example, but you'll probably be getting them from
your form.

 

[gmazza via AccessMonster.com]

The value I had for the lngTrialID taken from the form was wrong. Once I
corrected that it now works perfect.
Thank you sir, have a good one!

Thanks for your reply!
I was getting an error on this line:

[quoted text clipped - 8 lines]

that,
keeps adding 1 to 0.

Hmm, you're right that I had a typo there, and I see another minor glitch,
but your corrected form should not have given you 1 every time. This
revised version works for me, with the example data you posted:

lngNextValueNo = 1 + _
Nz(DMax("ValueNo", "TrialData", _
"TrialID=" & lngTrialID & _
" AND SequenceNo=" & lngSequenceNo), -1)

Note that, in the above, I used "TrialData" as the name of the table. You
must change that to the name of your table.

Please let me know if the above, modified appropriately, doesn't work for
you. Where are you getting the values of lngTrialID and lngSequenceNo? I
hard-coded them for the example, but you'll probably be getting them from
your form.