Updating field with query results

[Martin]

Hello,

I have a combo box that selects a name of a department. Once this has been
updated I want to add to the same form a unique work reference ID into a
unbound column. I have a query working out the unique reference and I have
manged tio run this after the update. Here is the code:

Private Sub RATeam_AfterUpdate()
Dim SQL, Result As String

DoCmd.RunSQL "SELECT

& IIf([RefNo Max] Is Null,'1',[RefNo Max]+1) AS
RefNo FROM qryTeamRefNo LEFT JOIN qryMaxofRefNo ON qryTeamRefNo.Team =
qryMaxofRefNo.[RA Team]"


Me!RefNo.Value = Result

End Sub

The problem I am having is that I do not know how to get the data from the
query into the unbound field call RefNo. Can anyone help?

Thank you.

Martin​

 

[Alex Dybenko]

hi,
try to use dlookup, make a new query with following SQL:
SELECT

& IIf([RefNo Max] Is Null,'1',[RefNo Max]+1) AS
RefNo FROM qryTeamRefNo LEFT JOIN qryMaxofRefNo ON qryTeamRefNo.Team =
qryMaxofRefNo.[RA Team]

and then use it as


Me!RefNo.Value= dlookup("RefNo ","MyNewQuery")


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com​

 

[Martin]

Alex,

This has worked perfectly. Thank you for pointing me in the right
direction, very much appreciated.

Martin

hi,
try to use dlookup, make a new query with following SQL:
SELECT

& IIf([RefNo Max] Is Null,'1',[RefNo Max]+1) AS
RefNo FROM qryTeamRefNo LEFT JOIN qryMaxofRefNo ON qryTeamRefNo.Team =
qryMaxofRefNo.[RA Team]

and then use it as


Me!RefNo.Value= dlookup("RefNo ","MyNewQuery")


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hello,

I have a combo box that selects a name of a department. Once this has
been
updated I want to add to the same form a unique work reference ID into a
unbound column. I have a query working out the unique reference and I
have
manged tio run this after the update. Here is the code:

Private Sub RATeam_AfterUpdate()
Dim SQL, Result As String

DoCmd.RunSQL "SELECT

& IIf([RefNo Max] Is Null,'1',[RefNo Max]+1)
AS
RefNo FROM qryTeamRefNo LEFT JOIN qryMaxofRefNo ON qryTeamRefNo.Team =
qryMaxofRefNo.[RA Team]"


Me!RefNo.Value = Result

End Sub

The problem I am having is that I do not know how to get the data from the
query into the unbound field call RefNo. Can anyone help?

Thank you.

Martin​

​

​