how to call a value from an opened query

M

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
 
T

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
 
M

miguel

Thank you,

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


Ted Allen said:
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 said:
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
 
T

Ted Allen

My pleasure, glad it helped.

-Ted Allen

miguel said:
Thank you,

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


Ted Allen said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top