SQL behind a form's control

[Rafi]

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?

 

[Brian Bastl]

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

 

[Brian Bastl]

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

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

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?