Re: populate subform after update

Tech-Archive recommends: Fix windows errors by optimizing your registry

runtime error '3075':
syntax error (missing operator) in query expression 'Active=True where NOT
EXISTS (SELECT*FROM tblitemsales WHERE tblitemsales.eventID ='21')'.

Private Sub Form_AfterUpdate()
Dim db As Database
Dim LSQL As String

'Establish connection to current database
Set db = CurrentDb()

LSQL = " INSERT Into tblitemsales(EventID, ItemsID)" & _
" SELECT '" & EventID & "', ItemsID " & _
" FROM tblItems " & _
" WHERE Active = True"
LSQL = LSQL & " WHERE NOT EXISTS " & _
"( SELECT * FROM tblitemsales WHERE tblitemsales.EventID = '" & EventID
& "')"


db.Execute LSQL, dbFailOnError

'Requery subform that new records are listing
frmsales_eventsubform.Requery
End Sub


--
Dan


"Dan" wrote:

run-time error'3061':
Too few parameters. expected 2.

debug highlights

db.Execute LSQL, dbFailOnError

I am very new to coding.
--
Dan


"John Spencer" wrote:

Yes, you have to execute the SQL statement

Private Sub Form_AfterInsert()
Dim db As Database
Dim LSQL As String

'Establish connection to current database
Set db = CurrentDb()

LSQL = " INSERT Into tblitemsales(EventID, ItemID)" & _
" SELECT '" & EventID & "', ItemID " & _
" FROM tblItems " & _
" WHERE ActiveItems = True"

Db.Execute LSQL, dbFailonError

'Requery subform that new records are listing
frmsales_eventsubform.Requery
End Sub



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:647F3A96-DAF5-4886-B38F-B9FD0B91913A@xxxxxxxxxxxxxxxx
here is the updated code, but I think I am missing something important as
nothing happens:

Private Sub Form_AfterInsert()
Dim db As Database
Dim LSQL As String

'Establish connection to current database
Set db = CurrentDb()

LSQL = " INSERT Into tblitemsales(EventID, ItemID)" & _
" SELECT '" & EventID & "', ItemID " & _
" FROM tblItems " & _
" WHERE ActiveItems = True"


'Requery subform that new records are listing
frmsales_eventsubform.Requery
End Sub
--
Dan


"John Spencer" wrote:

I think that would make sense. Why not backup your data base and then
try
it?

You could even add the where clause

LSQL = " INSERT Into tblitemsales(EventID, ItemID)" & _
" SELECT '" & EventID &"', ItemID " & _
" FROM tblItems " & _
" WHERE ActiveItems = True"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:98F62EDC-396D-45E8-82A6-FCCC77081B88@xxxxxxxxxxxxxxxx
actually qrysales is not a table, it is a query based on tblitemsales.
The
subform is based on qrysales. should i change the code to :
LSQL = " INSERT Into tblitemsales(EventID, ItemID)" & _
" SELECT '" & EventID &"', ItemID " & _
" FROM tblItems "
so that the changes are being made directly to the table opposed to the
qry?

tblitems does have a yes/no field for active items.

thanks


--
Dan


"John Spencer" wrote:

You could try to do this in one operation

LSQL = " INSERT Into QrySales(EventID, ItemID)" & _
" SELECT '" & EventID &"', ItemID " & _
" FROM tblItems "

The above should add one record to the table qrySales (strange name
for a
table)
for every record in tblItems.

Being paranoid I would either have a unique index on the combination
of
EventID and ItemID or would check for the existence of the records
before
adding them by using a where clause like the following.
LSQL = LSQL & " WHERE NOT EXISTS " & _
"( SELECT * FROM QrySales WHERE QrySales.EventID = '" & EventID
&"')"

If you might drop ITEMS, I would add an ARCHIVED field to tblItems.
Then
I
could check that value in the SELECT query to see if the item should
be
added to the qrySales table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:09535847-1780-413B-8D36-79FC66D2A75A@xxxxxxxxxxxxxxxx
I think I got it.

Private Sub Form_AfterUpdate()


Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 2

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 36

LSQL = "insert into qrysales (EventID, ItemID)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & EventID & "', " & LCntr & ")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmsales_eventsubform.Requery

End Sub
--
Dan


"Dan" wrote:

I have 1 form based on tblevents which tracks event information,
date,
time,
attendance etc... Tblevents has a unique eventID (autonumber). I
have
a
subform based on a query of a junction table which tracks product
sales.
the
query has fields itemsalesID,EventID,ItemID, sold,price and a total
calculation.

is it possible to set an after update event on the eventID field
which
will
populate the sub form with all items from the tblitems (30
available
items).
to do this I would have to update the query with the 30 items and
the
current
eventID. optionally I could use a control button to open a popup
form
with a
multiselect listbox, select all items and than update the query.

I currently use a combobox in the subform and select each item
individually,
which works but is tedious. I want all items listed as, each event
will
most
likely sell 1 of each item.

either case, I am too new to program this myself.

any pointers would be appreciated.

thanks
--
Dan









.

Quantcast