SQL behind a form's control

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


"Rafi" wrote in message
...
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?

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" wrote in message
...
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" wrote in message
...
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?