Re: Multiple SQL statements & return value


"Lewis" <Lewis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CCA3114C-85DA-4065-9217-0F190FC29B08@xxxxxxxxxxxxxxxx
Hello,
I have a somLast_nums table that keeps the last customer number used.
I have this statement which increments the customer number.
It returns the number of rows affected (1).

I need it to return the new customer number. Any ideas? Thanks

UPDATE SomLast_num
SET CUST_NUM = CUST_NUM + 1
WHERE (CurrentRec = 1)

Well you are either going to have to do SELECT into a Recordset or use a
temporary/permanent Stored procedure and return an Output Parameter (that
option is not possible with Access).

So

UPDATE SomLast_num SET CUST_NUM = CUST_NUM + 1 WHERE CurrentRec = 1
SELECT CUST_NUM FROM SomLast_num WHERE CurrentRec = 1

If you are using an adhoc query with a Command object and 1 Output
parameter, somthing like

SELECT ? = (SELECT CUST_NUM FROM SomLast_num WHERE CurrentRec = 1)

should work. The ? will be filled in with the return value and no RecordSet
need be constructed.

Stephen Howe


.

Relevant Pages

  • RE: How do I build a customer search?
    ... The code I posted will make the record from the form's recordset the current ... Dave Hargis, Microsoft Access MVP ... "Dustin" wrote: ... This will help our people know what our customer has and also I would like ...
    (microsoft.public.access.formscoding)
  • RE: Search facility query
    ... Set rstClone = Me.RecordsetClone ... MsgBox "No Matching Customer Found" ... The name of the table or query that is the record source for the main form. ... Are these field in a recordset or controls on a form? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Change Control Source
    ... recordset in code for the purposes of building the string. ... base your report on a query that pulls the appropriate customers, ... customer ID ...
    (microsoft.public.access.reports)
  • RE: How do I build a customer search?
    ... "Klatuu" wrote: ... If there are data you want to show that are not in the form's recordset, ... Dave Hargis, Microsoft Access MVP ... This will help our people know what our customer has and also I would like ...
    (microsoft.public.access.formscoding)
  • Re: > 100 is causing strange results
    ... result is a date, so you're trying to compare the number 100 to a date, a ... So, to simplify the output, I have the customer id, ... > appear in the resulting recordset even f the variable input is 50 days. ... > the records after the user input date, ...
    (microsoft.public.access.queries)