Solved DAO recordset from UNION query can’t find specific record

[slharman1]

Is it possible to declare a recordset based on a UNION query?

 

[arnelgp]

of course you can, but you won't be able to Edit or Append to it.

 

[theDBguy]

Maybe if you show us your code, we can tell if there's anything we can suggest.

 

[slharman1]

Maybe if you show us your code, we can tell if there's anything we can suggest.

DBGuy
Apparently my rs is empty.
I am trying to create it from a union query (qryWorkOrdersShop)
My union qry does not have a PK if that matters but the first field WOItems is a no duplicates field in its underlying table.
On error, when I hover over the highlighted line: Set rst = db.OpenRecordset("qryWorkOrdersShop", dbOpenDynanset) I get a popup message stating the db.OpenDynaset is empty

I am just trying to populate a txtform field on my form with the description field in the query.


Here is my code:

Private Sub cboShopItemID_AfterUpdate()
   
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim shopitem As Variant
    Dim order As Variant
       
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryWorkOrdersShop", dbOpenDynanset)
    Set shopitem = Me.cboShopItemID
    order = Left(shopitem, 5)
   
    rst.FindFirst "WOitems" & shopitem
            Me.txtJobName.Value = DLookup("JobName", "tblOrders", "OrderNumber =" & order)
            Me.txtDescription.Value = rst(Description)
       
   
End Sub

 

[Gasman]

You have to execute the line to see any values?
I mentioned this in the other thread

Might also want to lookup how to use FindFirst?

Recordset.FindFirst method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[theDBguy]

DBGuy
Apparently my rs is empty.
I am trying to create it from a union query (qryWorkOrdersShop)
My union qry does not have a PK if that matters but the first field WOItems is a no duplicates field in its underlying table.
On error, when I hover over the highlighted line: Set rst = db.OpenRecordset("qryWorkOrdersShop", dbOpenDynanset) I get a popup message stating the db.OpenDynaset is empty

I am just trying to populate a txtform field on my form with the description field in the query.


Here is my code:

Private Sub cboShopItemID_AfterUpdate()
  
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim shopitem As Variant
    Dim order As Variant
      
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryWorkOrdersShop", dbOpenDynanset)
    Set shopitem = Me.cboShopItemID
    order = Left(shopitem, 5)
  
    rst.FindFirst "WOitems" & shopitem
            Me.txtJobName.Value = DLookup("JobName", "tblOrders", "OrderNumber =" & order)
            Me.txtDescription.Value = rst(Description)
      
  
End Sub

Hi. I agree. Hovering on a line that hasn't executed yet will not give you an accurate value at that point in time or the code.

 

[slharman1]

DBGuy
Apparently my rs is empty.
I am trying to create it from a union query (qryWorkOrdersShop)
My union qry does not have a PK if that matters but the first field WOItems is a no duplicates field in its underlying table.
On error, when I hover over the highlighted line: Set rst = db.OpenRecordset("qryWorkOrdersShop", dbOpenDynanset) I get a popup message stating the db.OpenDynaset is empty

I am just trying to populate a txtform field on my form with the description field in the query.


Here is my code:

Private Sub cboShopItemID_AfterUpdate()
   
End Sub

OK revised my code but now I can't get to the correct record. What am I doing wrong?

Private Sub cboShopItemID_AfterUpdate()
   
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim shopitem As Variant
    Dim order As Variant
       
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryWorkOrdersShop")
    Set shopitem = Me.cboShopItemID
    order = Left(shopitem, 5)
   
    rst.FindFirst ("WOitems" = shopitem)
            Me.txtJobName.Value = DLookup("JobName", "tblOrders", "OrderNumber =" & order)
            Me.txtDescription.Value = rst!Description.Value
       
   
End Sub

You have to execute the line to see any values?
I mentioned this in the other thread

Might also want to lookup how to use FindFirst?

Recordset.FindFirst method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

You have to execute the line to see any values?
I mentioned this in the other thread

Might also want to lookup how to use FindFirst?

Recordset.FindFirst method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

I couldn't get past the set recordset line due to trying to open it as a db.opendynaset type.
I am past that now but can't get to the record I want to use.

 

[Gasman]

For all the comments on concatenation in that other thread, you just go ahead and do the same thing in this thread?

You did not read much of the link I gave you did you, an example is actually on there?

 

[slharman1]

You have to execute the line to see any values?
I mentioned this in the other thread

Might also want to lookup how to use FindFirst?

Recordset.FindFirst method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

So for .FindFirst, I have to .cone to a new recordset with only one record, is that how's it's done?

 

[Gasman]

So for .FindFirst, I have to .cone to a new recordset with only one record, is that how's it's done?

No. Little point in having a Find First in that case, would it?, if there is only one record?

 

[slharman1]

No. Little point in having a Find First in that case, would it?, if there is only one record?

So findfirst then clone then bookmark the record?

 

[Gasman]

Where is clone and bookmark coming from?
I was just addressing getting FindFirst to work, as per thread title.

 

[isladogs]

Whilst it is perfectly possible, why are you bothering with a recordset anyway?
Just filter your UNION query to get the record you need. Much easier

 

[slharman1]

Ok so I did this and locked up access, I am assuming in a continuous loop:

Private Sub cboShopItemID_AfterUpdate()
   
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim shopitem As Variant
    Dim order As Variant
       
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryWorkOrdersShop")
    Set shopitem = Me.cboShopItemID
    order = Left(shopitem, 5)
   
 
            Me.txtJobName.Value = DLookup("JobName", "tblOrders", "OrderNumber =" & order)
         
           
     
       
      
          Me.txtDescription = rs!Descrption
         
       
   
       
   
End Sub

Whilst it is perfectly possible, why are you bothering with a recordset anyway?
Just filter your UNION query to get the record you need. Much easier

I don't know how to do that, can you show me?

 

[Cronk]

Re #7, that should be

rst.FindFirst ("WOitems = " & shopitem)

if WOintems is numeric

 

[Gasman]

Ok so I did this and locked up access, I am assuming in a continuous loop:

Private Sub cboShopItemID_AfterUpdate()
  
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim shopitem As Variant
    Dim order As Variant
      
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryWorkOrdersShop")
    Set shopitem = Me.cboShopItemID
    order = Left(shopitem, 5)
  

            Me.txtJobName.Value = DLookup("JobName", "tblOrders", "OrderNumber =" & order)
        
          
    
      
     
          Me.txtDescription = rs!Descrption
        
      
  
      
  
End Sub

I don't know how to do that, can you show me?

There is no loop there?

 

[slharman1]

For all the comments on concatenation in that other thread, you just go ahead and do the same thing in this thread?

You did not read much of the link I gave you did you, an example is actually on there?

I read the entire page, I just don't get what I am doing wrong.
I am starting to think my union query is not built correctly.
although I can run it and see it - it is not editable.
do I have to declare my rs as snapshot?

 

[isladogs]

Union queries are read only.

Re post #14, if you can do a union query then I'm sure you can do a select query based on that with a WHERE condition
For example, create a new select query something like

SELECT * FROM qryYourUnionQueryName WHERE OrderNumber = order

See SQL WHERE Clause (w3schools.com)

 

[slharman1]

Re #7, that should be

rst.FindFirst ("WOitems = " & shopitem)

if WOintems is numeric

That doesn't work, but WOItems is a string or variant, it is a field in my query concatenated from a long field and (2) short text fields.

 

[Pat Hartman]

We keep trying to move you away from FindFirst. But to fix the problem, try enclosing the variable in quotes.

rst.FindFirst ("WOitems = '" & shopitem & "'")

It is really hard to get a real solution when you question is about how to fix your code. If you can tell us in non-technical terms what your objective is, we can offer the best soltuion.