Updating field with query results

  • Thread starter Thread starter Martin
  • Start date Start date
M

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​
 
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​
 
Alex,

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

Martin

Alex Dybenko said:
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 said:
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​
Click to expand...

​
Click to expand...
​
 
Back
Top