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

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 

"8l2255" wrote:

> 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 wrote:
>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.

-- 
Marsh
MVP [MS Access]

Thanks Marshall.

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

"Marshall Barton" wrote:

> 8l2255 wrote:
> >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.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

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.

"Marshall Barton" wrote:

> 8l2255 wrote:
> >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.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

8l2255 wrote:
>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.
 
-- 
Marsh
MVP [MS Access]