A
apedersen
Hello,
I'm trying to create a dynamic numbering system for a continuous form
so I can reorder the records and control where new records are placed.
but i'm having trouble, and i think it has to do with dlookup.
here's the flow.
one continuous form - frmFind - gathers search keywords from the user
and returns the hits those keywords got. a button on each of the
records returned then allows the user to insert any of those records
into a table called tblSequence that is then viewed in a second
continuous form - frmSequence.
The subroutine in frmFind that I use to INSERT the new record INTO
tblSequence also generates a primary key value for the new tblSequence
record. I do this through the variable intSortkey.
Everything works so far.
Now, though, I'd like to tweak it so that when a new record is added to
tblSequence, it shows up in frmSequence directly below the active
record in frmSequence. In other words, I want the inserted record's
intSortkey value to be one more than the intSortkey value of the active
record in frmSearch.
Here's how I'm trying to go about it.
when frmSequence loses focus:
dim intHighlight as integer
intHighlight = DLookup("[KEY]", "tblSequence", "[KEY] = " & Me!KEY)
then, when another record is added to tblSequence from frmFind, this
code runs:
intSortkey = intHighlight + 1
strSQL = "INSERT INTO frmSequence ([KEY] ...
strSQL = strSQL & "VALUES(" & intSortkey & ...
[Forms]![frmSequence].Requery
In my head, it should work beautifully. In practice, all of my
intSortkeys have a value of 1.
Like I said, I suspect the trouble is with my dlookup. I've tried many
syntax variations, including using frmSequence instead of tblSequence,
but to no avail.
Perhaps I should be using a recordset. (But again, i'm unsure of the
syntax and phrasing needed to get dlookup to find a value in the
recordset's active record)
Any help greatly appreciated.
-Andy
I'm trying to create a dynamic numbering system for a continuous form
so I can reorder the records and control where new records are placed.
but i'm having trouble, and i think it has to do with dlookup.
here's the flow.
one continuous form - frmFind - gathers search keywords from the user
and returns the hits those keywords got. a button on each of the
records returned then allows the user to insert any of those records
into a table called tblSequence that is then viewed in a second
continuous form - frmSequence.
The subroutine in frmFind that I use to INSERT the new record INTO
tblSequence also generates a primary key value for the new tblSequence
record. I do this through the variable intSortkey.
Everything works so far.
Now, though, I'd like to tweak it so that when a new record is added to
tblSequence, it shows up in frmSequence directly below the active
record in frmSequence. In other words, I want the inserted record's
intSortkey value to be one more than the intSortkey value of the active
record in frmSearch.
Here's how I'm trying to go about it.
when frmSequence loses focus:
dim intHighlight as integer
intHighlight = DLookup("[KEY]", "tblSequence", "[KEY] = " & Me!KEY)
then, when another record is added to tblSequence from frmFind, this
code runs:
intSortkey = intHighlight + 1
strSQL = "INSERT INTO frmSequence ([KEY] ...
strSQL = strSQL & "VALUES(" & intSortkey & ...
[Forms]![frmSequence].Requery
In my head, it should work beautifully. In practice, all of my
intSortkeys have a value of 1.
Like I said, I suspect the trouble is with my dlookup. I've tried many
syntax variations, including using frmSequence instead of tblSequence,
but to no avail.
Perhaps I should be using a recordset. (But again, i'm unsure of the
syntax and phrasing needed to get dlookup to find a value in the
recordset's active record)
Any help greatly appreciated.
-Andy