SQL behind a form's control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a textbox control (PO) on a form (frmInvoice) which I would like to
populate with the result of a query.

SELECT TblInvoice.[Purchase Order]
FROM TblInvoice
WHERE (TblInvoice.InvoiceID)= (SELECT Max(InvoiceID) FROM TblInvoice
WHERE([TblInvoice].[Company_ID]=[Forms]![FrmInvoice]![Company_ID]));

I would like to associate the result of querry above with the On Enter event
of the control (PO) so that the querry's result will become the control's
default value.

Can somebody help with the VBA syntax?
 
Why not just use the DMax function instead? Put it in the form's On Current
event.

Me.(PO).DefaultValue =
nz(Dmax("InvoiceID","TblInvoice","[TblInvoice].[CompanyID =" &
Me.CompanyID),0)

Brian
 
Or if you prefer,

Dim db As Dao.Database
Dim rs as Dao.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TblInvoice.[Purchase Order] .....

Me.PO.DefaultValue = rs!InvoiceID

rs.Close
db.Close
set db = Nothing

Brian

Brian Bastl said:
Why not just use the DMax function instead? Put it in the form's On Current
event.

Me.(PO).DefaultValue =
nz(Dmax("InvoiceID","TblInvoice","[TblInvoice].[CompanyID =" &
Me.CompanyID),0)

Brian


Rafi said:
I have a textbox control (PO) on a form (frmInvoice) which I would like to
populate with the result of a query.

SELECT TblInvoice.[Purchase Order]
FROM TblInvoice
WHERE (TblInvoice.InvoiceID)= (SELECT Max(InvoiceID) FROM TblInvoice
WHERE([TblInvoice].[Company_ID]=[Forms]![FrmInvoice]![Company_ID]));

I would like to associate the result of querry above with the On Enter event
of the control (PO) so that the querry's result will become the control's
default value.

Can somebody help with the VBA syntax?
Click to expand...
Click to expand...
 
Back
Top