OpenForm to show Last n Records

[8l2255]

Hi Everyone,

I have form 'AddMultipleItems' with fields as follows

CategoryID Number
ProductID Number
Date_Acquired Date/Time
StatusID Number
txtN unbound number

When you click the Create button it adds txtN amount of records to 'Itemtbl'
with (~)fields filled in

Itemtbl has information

~ItemID Autonumber
~CategoryID Number
~ProductID Number
~Date_Acquired Date/Time
~StatusID Number
Serial_Number Text
AssetNo Text
Comments Text

The purpose of this is to allow a person to create multiple items with the
same product details in one hit and then open a form which lists the items
just created with only the Serial Number and Asset No fields to update so a
barcode scanner can be employed.

I was looking at something like the below but am fairly certain i am offroad
with this one.

Dim stDocName As String
Dim stLinkCriteria As String
Dim txtnn As Integer
txtnn = Me!TxtN

stDocName = "multipleitemadddetails"

stoops = "SELECT TOP txtnn ItemID FROM [itemtbl] ORDER BY ItemID DESC;"

DoCmd.openform stDocName, , , , , , stoops

Any help appreciated! Let me know if you need more info -

Lou

 

[8l2255]

Just to clarify,

I have the code

Dim db As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("itemtbl")
With rs
For a = 1 To TxtN
..AddNew
!ProductID = ProductID
!CategoryID = CategoryID
!StatusID = 1
!Date_Acquired = Date_Aquired
..Update
Next a
End With
rs.close

to create records in the itemtbl and what i am stuck with is how to open a
new form which shows the last txtN amount of records (top txtn itemid order
by itemid desc) was kinda where i was trying to head... i'm not really sure
about this one as you might be able to tell!

Thanks

 

[Marshall Barton]

I have form 'AddMultipleItems' with fields as follows

CategoryID Number
ProductID Number
Date_Acquired Date/Time
StatusID Number
txtN unbound number

When you click the Create button it adds txtN amount of records to 'Itemtbl'
with (~)fields filled in

Itemtbl has information

~ItemID Autonumber
~CategoryID Number
~ProductID Number
~Date_Acquired Date/Time
~StatusID Number
Serial_Number Text
AssetNo Text
Comments Text

The purpose of this is to allow a person to create multiple items with the
same product details in one hit and then open a form which lists the items
just created with only the Serial Number and Asset No fields to update so a
barcode scanner can be employed.

I was looking at something like the below but am fairly certain i am offroad
with this one.

Dim stDocName As String
Dim stLinkCriteria As String
Dim txtnn As Integer
txtnn = Me!TxtN

stDocName = "multipleitemadddetails"

stoops = "SELECT TOP txtnn ItemID FROM [itemtbl] ORDER BY ItemID DESC;"

DoCmd.openform stDocName, , , , , , stoops

That's actually pretty close. Try this:

stoops = "SELECT TOP " & txtnn & " * " _
& "FROM itemtbl ORDER BY ItemID DESC"
DoCmd.openform stDocName, , , , , , stoops


Then, in the form's Open event:

Me.RecordSource = Me.OpenArgs

However, there is one concern. Where does the value of
ItemID come from and how do you guarantee that the new
ItemID values are the top ones? If it's an AutoNumber field
in the table, there are no guarantees because they can go
negative. OTOH, if you are generating the ItemID in your
code, then you don't need to use a TOP query because you can
remember the ID of the first record added and the query can
retrieve all records with ItemID >= the first added ItemID
using WhereCondition instead of OpenArgs.

 

[8l2255]

Thanks Marshall.

When I use that code i receive an error "expected end of statement" for the
" * " component.

I have form 'AddMultipleItems' with fields as follows

CategoryID Number
ProductID Number
Date_Acquired Date/Time
StatusID Number
txtN unbound number

When you click the Create button it adds txtN amount of records to 'Itemtbl'
with (~)fields filled in

Itemtbl has information

~ItemID Autonumber
~CategoryID Number
~ProductID Number
~Date_Acquired Date/Time
~StatusID Number
Serial_Number Text
AssetNo Text
Comments Text

The purpose of this is to allow a person to create multiple items with the
same product details in one hit and then open a form which lists the items
just created with only the Serial Number and Asset No fields to update so a
barcode scanner can be employed.

I was looking at something like the below but am fairly certain i am offroad
with this one.

Dim stDocName As String
Dim stLinkCriteria As String
Dim txtnn As Integer
txtnn = Me!TxtN

stDocName = "multipleitemadddetails"

stoops = "SELECT TOP txtnn ItemID FROM [itemtbl] ORDER BY ItemID DESC;"

DoCmd.openform stDocName, , , , , , stoops

That's actually pretty close. Try this:

stoops = "SELECT TOP " & txtnn & " * " _
& "FROM itemtbl ORDER BY ItemID DESC"
DoCmd.openform stDocName, , , , , , stoops


Then, in the form's Open event:

Me.RecordSource = Me.OpenArgs

However, there is one concern. Where does the value of
ItemID come from and how do you guarantee that the new
ItemID values are the top ones? If it's an AutoNumber field
in the table, there are no guarantees because they can go
negative. OTOH, if you are generating the ItemID in your
code, then you don't need to use a TOP query because you can
remember the ID of the first record added and the query can
retrieve all records with ItemID >= the first added ItemID
using WhereCondition instead of OpenArgs.

 

[8l2255]

my mistake! i think i entered the code wrong the first time.

However, when the form opens it just brings up #Name? in all the fields.

I have form 'AddMultipleItems' with fields as follows

CategoryID Number
ProductID Number
Date_Acquired Date/Time
StatusID Number
txtN unbound number

When you click the Create button it adds txtN amount of records to 'Itemtbl'
with (~)fields filled in

Itemtbl has information

~ItemID Autonumber
~CategoryID Number
~ProductID Number
~Date_Acquired Date/Time
~StatusID Number
Serial_Number Text
AssetNo Text
Comments Text

The purpose of this is to allow a person to create multiple items with the
same product details in one hit and then open a form which lists the items
just created with only the Serial Number and Asset No fields to update so a
barcode scanner can be employed.

I was looking at something like the below but am fairly certain i am offroad
with this one.

Dim stDocName As String
Dim stLinkCriteria As String
Dim txtnn As Integer
txtnn = Me!TxtN

stDocName = "multipleitemadddetails"

stoops = "SELECT TOP txtnn ItemID FROM [itemtbl] ORDER BY ItemID DESC;"

DoCmd.openform stDocName, , , , , , stoops

That's actually pretty close. Try this:

stoops = "SELECT TOP " & txtnn & " * " _
& "FROM itemtbl ORDER BY ItemID DESC"
DoCmd.openform stDocName, , , , , , stoops


Then, in the form's Open event:

Me.RecordSource = Me.OpenArgs

However, there is one concern. Where does the value of
ItemID come from and how do you guarantee that the new
ItemID values are the top ones? If it's an AutoNumber field
in the table, there are no guarantees because they can go
negative. OTOH, if you are generating the ItemID in your
code, then you don't need to use a TOP query because you can
remember the ID of the first record added and the query can
retrieve all records with ItemID >= the first added ItemID
using WhereCondition instead of OpenArgs.

 

[Marshall Barton]

However, when the form opens it just brings up #Name? in all the fields.

It sounds like you did not set the form's RecordSource in
its record source or the form controls sre not bound to the
fields in Itemtbl.

You might get more information if you put a break point on
the form's Open Open event and test. When the code stops,
Copy the SQL string from OpenArgs to a new query's SQL view
and open the query.