populate subform after update

[Guest]

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

 

[Guest]

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

 

[Guest]

so this formula will work until I add new items and delete old items, the
subform will endup showing discontinued items. how do I work around it?

 

[John Spencer]

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

 

[Guest]

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

 

[John Spencer]

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

 

[Guest]

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

 

[John Spencer]

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

 

[Guest]

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

debug highlights

db.Execute LSQL, dbFailOnError

I am very new to coding.

 

[Guest]

found the problem, I was missing a letter in the field name. BUt now the
issue is adding the where statement so i don't get duplicate items in the
subform.

any ideas

 

[Guest]

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

 

[Guest]

would it also be possible to include in this code, a way to pull the price
from tblitems and insert into tblitemsales. So I can keep accurate price
changes?

thanks

 

[John Spencer]

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


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

 

[Guest]

Thanks John, you've been a huge help.
--
Dan

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


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