Use particular record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all, not too good at this but i am using the following statement to use
the last record :
strSql = "SELECT * FROM tbl_CIANumberRequest ORDER BY dtAdded DESC"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql, , dbReadOnly)

strReqNo = rst!RequestID
strReqC = rst!RequestorCName
strReqS = rst!RequestorSName
strName = strReqC & " " & strReqS
strReqD = rst!RequestorDept
curAmnt = rst!Amount
strTlDesc = rst!ToolingDescription
strGams = Nz(rst!gAMSNo, "N/A")
strProj = Nz(rst!ProjectNo, "N/A")
strPrtNo = Nz(rst!PartNumber, "N/A")
strChgNo = Nz(rst!ChangeNo, "N/A")
strAss = Nz(rst!AssetNumber, "None")
strNew = Nz(rst!NewTool, "")
strOld = Nz(rst!OldTool, "")
strCur = rst!Currency

This selects the last record and the other fields are for input in a message.

But what i would like to do is ask the user which record he would like to
use, so the input box would request the number of the record then i use that
record for the information pertaining to it.

Any help would be greatly appreciated..
 
Les

I may be overlooking something...

The SQL statement looks to return ALL records, in descending order (by
dtAdded).

When your code then opens a recordset based on that SQL, you get all
records. If the table is large, and you only want a single record, this is
.... hmmm, "inefficient". You could use the "TOP" command to get as few as
you want -- something like:

SELECT TOP 1 ...

Moreover, your code doesn't use something like:
rst.MoveFirst
to ensure that you are on the correct record, so how do you know that you
have the right one?

If what you want is to give the user a way to enter (better still, to
SELECT) the record they wish to see more detail on, there's an easier way to
do this.

Post back if you are looking for more information.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff,

What i am doing is sending this last record as it is requesting a number
from another user and the problem is that sometimes the reciever says that he
did not recieve it, so i want to give the user the choice of re-sending his
request. Shouls i rather use a query to do this ??
 
Les

I may be reading too much into your description...

It sounds like you want to give a user the ability to select the most-recent
request OF THAT USER. And are you sure that someone would never say "I got
your last request, but the one before that never arrived."?

Another way to approach this would be to use a form that will display a
single request (base the form on a query). In the header, add a combo box
(also based on a query) that returns enough rows to let the user pick the
request s/he wishes to see. In the form's query, base the query's ID on the
value in the combo box. And in the combobox's AfterUpdate event, requery
the form.

This approach gives an "empty" form to start with (nothing selected in the
combobox, so that's the record displayed), followed by selection of a
record/request and the reloading of the form with that request displayed.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top