Running different queries dependent on another query output

  • Thread starter Thread starter Pawe³ Wróbel
  • Start date Start date
P

Pawe³ Wróbel

Hello!
My situation looks like this:
I have a table with columns: id_user, id_product, amount
I have a form, on which I can choose usera, product and input amount.

And the problem:
After I press the button, I would like Access to check if there is a record
with a combiantion of given id_user and id_product. If exists then it should
add inputed amount to existing one. If not, then it should add new record. I
have a query that checks if such combiantion exists (returns position id), I
have a query that changes the amount and have a query that adds a new
record. But I'm not able to connect it into one solution.

Maye someone knows how to check if given query returns any rows or not, and
if not then run specified query.

Please help, I've spent 2 day on it

Pawel
 
Pawe³,

I think it is easier to do it all in code.
You will need to change the table name and the control names in the code
where there is an '@ at the end of the line.

Watch for line wrap

------BEGIN CODE ------------
Dim db As DAO.database
Dim rst As DAO.Recordset
Dim tmpAmount As Currency
Dim RecFound As Boolean
Dim strSQL As String

Set db = CurrentDb()

' your first query is a select query, so
strSQL = "SELECT Amount, id_userFK, id_productFK FROM YourTable"
'@
strSQL = strSQL & " WHERE id_userFK = " & [Forms]![Form2].[cboUserID]
'@
strSQL = strSQL & " AND id_productFK = " &
[Forms]![Form2].[cboProductID] '@

Set rst = db.OpenRecordset(strSQL)

RecFound = Not (rst.BOF And rst.EOF)

If RecFound Then
' UPDATE amount
With rst
.Edit
' add the new amount to the old amount
!amount = !amount + Forms!Form2.curAmount '@
.Update
End With
Else
' INSERT a new record
With rst
.AddNew
!id_userFK = Forms!Form2.cboUserID '@
!id_productFK = Forms!Form2.cboProductID '@
!amount = Forms!Form2.curAmount '@
.Update
End With
End If
rst.Close
Set rst = Nothing
Set db = Nothing

------END CODE ------------


HTH

Steve
 
Back
Top