how to call a value from an opened query

[miguel]

Private Sub palletnr_LostFocus()

Dim stDocName As String

stDocName = "qry_stock" ( this opens a query which has the values >I
need)
DoCmd.OpenQuery stDocName, acNormal, acEdit
If stDocName <> "" Then

Dim dbr As Database
Dim rstinit_n As Recordset

Set dbr = CurrentDb() ( This creates a unique nr for a new record)
Set rstinit_n = dbr.OpenRecordset("init_n")
rstinit_n.Edit
rstinit_n!init_opdr_n = rstinit_n!init_opdr_n + 1
rstinit_n.Update

Dim db As Database
Dim rst As Recordset
Dim tsql As String
Dim cur_trailernr As String

Set db = CurrentDb()
'cur_trailernr = Me.TrailerNr
Set rst = db.OpenRecordset("SELECT * FROM transakt ") this is the
table where i want to ad a record and fill most of the fields up with data
from the same table but a different record)



rst.AddNew

rst.Fields("nr").Value = rstinit_n!init_opdr_n
rst.Fields("codetrans").Value = 2
rst.Fields("artnaam").Value =
[Forms]![frm_miscelanious_group]![palletnr].Value
rst.Fields("docnr") = [query]![qry_stock]![DOCNR] (here's the
problem how do I call this data the query is opened i can see the data i need
but i do not know how to call this data)

rst.Update

Me.refresh
DoCmd.Close acQuery, stDocName, acSaveNo

End If

 

[Ted Allen]

Hi Miguel,

To read the values from the query, you should open a recordset based on the
query, rather than opening the query itself. Opening a recordset based on a
query is the same as opening one based on a table (if using the database
openrecordset method as your code is), you just use the query name in place
of the table name. Then, you can navigate through the recordset and read
field values just as you have with your other recordsets. Of course, you
would only be able to update the records in the query if it is an updateable
query, but you can always read the values, which I think is what you want to
do.

HTH, Ted Allen

 

[miguel]

Thank you,

this is exactely what I needed to do ....

Hi Miguel,

To read the values from the query, you should open a recordset based on the
query, rather than opening the query itself. Opening a recordset based on a
query is the same as opening one based on a table (if using the database
openrecordset method as your code is), you just use the query name in place
of the table name. Then, you can navigate through the recordset and read
field values just as you have with your other recordsets. Of course, you
would only be able to update the records in the query if it is an updateable
query, but you can always read the values, which I think is what you want to
do.

HTH, Ted Allen

Private Sub palletnr_LostFocus()

Dim stDocName As String

stDocName = "qry_stock" ( this opens a query which has the values >I
need)
DoCmd.OpenQuery stDocName, acNormal, acEdit
If stDocName <> "" Then

Dim dbr As Database
Dim rstinit_n As Recordset

Set dbr = CurrentDb() ( This creates a unique nr for a new record)
Set rstinit_n = dbr.OpenRecordset("init_n")
rstinit_n.Edit
rstinit_n!init_opdr_n = rstinit_n!init_opdr_n + 1
rstinit_n.Update

Dim db As Database
Dim rst As Recordset
Dim tsql As String
Dim cur_trailernr As String

Set db = CurrentDb()
'cur_trailernr = Me.TrailerNr
Set rst = db.OpenRecordset("SELECT * FROM transakt ") this is the
table where i want to ad a record and fill most of the fields up with data
from the same table but a different record)



rst.AddNew

rst.Fields("nr").Value = rstinit_n!init_opdr_n
rst.Fields("codetrans").Value = 2
rst.Fields("artnaam").Value =
[Forms]![frm_miscelanious_group]![palletnr].Value
rst.Fields("docnr") = [query]![qry_stock]![DOCNR] (here's the
problem how do I call this data the query is opened i can see the data i need
but i do not know how to call this data)

rst.Update

Me.refresh
DoCmd.Close acQuery, stDocName, acSaveNo

End If

 

[Ted Allen]

My pleasure, glad it helped.

-Ted Allen

Thank you,

this is exactely what I needed to do ....

Hi Miguel,

To read the values from the query, you should open a recordset based on the
query, rather than opening the query itself. Opening a recordset based on a
query is the same as opening one based on a table (if using the database
openrecordset method as your code is), you just use the query name in place
of the table name. Then, you can navigate through the recordset and read
field values just as you have with your other recordsets. Of course, you
would only be able to update the records in the query if it is an updateable
query, but you can always read the values, which I think is what you want to
do.

HTH, Ted Allen

Private Sub palletnr_LostFocus()

Dim stDocName As String

stDocName = "qry_stock" ( this opens a query which has the values >I
need)
DoCmd.OpenQuery stDocName, acNormal, acEdit
If stDocName <> "" Then

Dim dbr As Database
Dim rstinit_n As Recordset

Set dbr = CurrentDb() ( This creates a unique nr for a new record)
Set rstinit_n = dbr.OpenRecordset("init_n")
rstinit_n.Edit
rstinit_n!init_opdr_n = rstinit_n!init_opdr_n + 1
rstinit_n.Update

Dim db As Database
Dim rst As Recordset
Dim tsql As String
Dim cur_trailernr As String

Set db = CurrentDb()
'cur_trailernr = Me.TrailerNr
Set rst = db.OpenRecordset("SELECT * FROM transakt ") this is the
table where i want to ad a record and fill most of the fields up with data
from the same table but a different record)



rst.AddNew

rst.Fields("nr").Value = rstinit_n!init_opdr_n
rst.Fields("codetrans").Value = 2
rst.Fields("artnaam").Value =
[Forms]![frm_miscelanious_group]![palletnr].Value
rst.Fields("docnr") = [query]![qry_stock]![DOCNR] (here's the
problem how do I call this data the query is opened i can see the data i need
but i do not know how to call this data)

rst.Update

Me.refresh
DoCmd.Close acQuery, stDocName, acSaveNo

End If