Best way to get value from a query

[Kryst51]

I built a query in the QBE to find the max value of a particular table that has a date/time stamp. The query returns one record (a PKID autonumber) with one field. I want to use this number to populate a field on my form using vba in the before update event.

I am at a loss as to how to do this however.

After some research I thought I might need to do something with a recordset using ADO or something and have a book that tries to teach how to use it, but I don't understand it all. And was wondering if there is an easy way that does not require me to learn this new method.

Or do I need to forge ahead and learn how to use ADO?

 

[boblarson]

You can use a recordset or a Dlookup.

Let me know if you need further hints.

 

[Kryst51]

Thanks Bob,

I have decided to try to learn the ADO method.... (As I think it will be most beneficial for me in the long run to learn)

So far, as I gather, I have to open a connection, then I can make my recordset. Here's what I think I need...

1. declare the connection as ADODB.connection
2. Set the connection = New ADODB.Connection
3. Dim a connection string then set the connection string.... I don't understand where the info from a connection string comes from. Is there a way to just say something like "CurrentDB" which I saw some where? Or do I have to actually use the type of thing my book is showing me? Something like: "Provider=Microsoft.Jet.....etc." As I have know clue how to pick this stuff for my setup.

 

[DCrake]

Step 1: Declare the connection name as a public in a standard module

Public MasterDbConn  As New ADODB.Connection

Step 2: Use the Set command to define it

Set MasterDbConn = New ADODB.Connection

Step 3:Use it in your app

Dim Tbl As New ADODB.Recordset

Set Tbl = New ADODB.Recordset
            sSql = "Your SQL Select statement here"            
        With Tbl
            .Open sSql, MasterDbConn, adOpenKeyset, adLockOptimistic, adCmdText

    Do something here
         End With
Set Tbl = Nothing

At the end of your session in the application you then need to close the MasterDbConn. Leaving it open thoughout the session means you only need to open it once.

 

[boblarson]

For the connection, do NOT use anything but

CurrentProject.Connection

if you are connecting to the same database that the code is in.

 

[DCrake]

Bob,

My suggestion comes from VB and of course I should have mentioned about your previous comment.

 

[Kryst51]

For the connection, do NOT use anything but

CurrentProject.Connection

if you are connecting to the same database that the code is in.

Do you mean that instead of building the module, I can just put in my code what you wrote above?

.open rsSQL, CurrentProject.Connection

etc.

 

[boblarson]

what I meant is not building a connection string. If you do that you will end up with errors. So you can assign to a variable:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

and then you can use cnn in your rst.Open code.

or you can simply refer to

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 

[Kryst51]

Here is what I have so far:

My Module:

Option Compare Database
Option Explicit
Public MasterDbConn As New ADODB.Connection
Set MasterDbConn = CurrentProject.Connection

Which I don't think is right. Don't I have to end it somehow? When I try it like this I get a compile error that highlights "Set" and says "Invalid outside procedure" But I am not sure how to end this. I tried End Public, & End Function. It's not a Sub so I know that wouldn't work. Which makes me wonder if I am missing something important in the first line (Public Master....)

Ok... Then I am calling it from the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim rsMaxRTId As ADODB.Recordset
Dim rsMaxRTIDSQL As String
Dim MaxRTID As Integer
Dim InsertRTSQL As String
 
Set rsMaxRTId = New ADODB.Recordset
rsMaxRTIDSQL = "SELECT tblTagRelationships.pkTagRelationshipID" * _
               " FROM qryTagRelationshipMax" & _
               " INNER JOIN tblTagRelationships" & _
               " ON qryTagRelationshipMax.MaxOfDateTimeStamp" & _
               " = tblTagRelationships.DateTimeStamp;"
 
    With rsMaxRTId
        .Open rsMaxRTIDSQL, MasterDbConn, adOpenKeyset, adLockOptimistic, adCmdText
    MaxRTID = pkTagRelationshipID
 
    Set rsMaxRTId = Nothing
    End With
'----------------------------------------------------------
'I am not concerned with this right now    
InsertRTSQL = "Insert tblTags(fkTagRelationshipID)" & _
              " Values (MaxRTID)" & _
              " Where pkTagID = Me.pkTagID;"
 
CurrentDb.Execute (InsertRTSQL)
 
End Sub

I haven't tested the rest of the code, the Insert and Currentdb.execute parts yet..... So am concerned, for the purposes of this thread, with the the code above that section.

Edit: You posted while I was typing this.... I need to digest and rework

 

[boblarson]

You can't instantiate an object in the general declarations section. Move the

Set MasterDbConn = CurrentProject.Connection

into your function.

 

[boblarson]

and pardon the short responses. I'm on the bus going to work and it is hard to type much.

 

[Kryst51]

and pardon the short responses. I'm on the bus going to work and it is hard to type much.

No problem Thank you for your patience.

 

[Kryst51]

Everything seems to run smoothly now until I get to the part where I try to use the data the query has returned. As I had a couple of typos in my previous code, here is the corrected:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Set MasterDbConn = CurrentProject.Connection
Dim rsMaxRTId As ADODB.Recordset
Dim rsMaxRTIDSQL As String
Dim MaxRTID As Integer
Dim InsertRTSQL As String
 
Set rsMaxRTId = New ADODB.Recordset
rsMaxRTIDSQL = "SELECT tblTagRelationships.pkTagRelationshipID" & _
               " FROM qryTagRelationshipMax" & _
               " INNER JOIN tblTagRelationships" & _
               " ON qryTagRelationshipMax.MaxOfDateTimeStamp" & _
               " = tblTagRelationships.DateTimeStamp;"
 
    With rsMaxRTId
        .Open rsMaxRTIDSQL, MasterDbConn, adOpenKeyset, adLockOptimistic, adCmdText
 
[COLOR=red]MaxRTID = pkTagRelationshipID[/COLOR]
    
    Set rsMaxRTId = Nothing
    End With
 
InsertRTSQL = "Insert Into tblTags(fkTagRelationshipID)" & _
              " Values (MaxRTID)" & _
              " Where pkTagID = Me.pkTagID;"
 
              CurrentDb.Execute (InsertRTSQL)
 
End Sub

I read in my book that once I opened a recordset I could refer to its fields like I did with the red line above. But when I try this I get an error of "variable not defined" so I checked my book, and changed the red line above to "MaxRTID = rsMaxRTId.pkTagRelationshipID" and now I get an error of "Compile Error: Method or data member not found".

EDIT: NEVERMIND, I Changed the period to a bang and it gets past this line now!

 

[boblarson]

Either use

rsMaxRTId!pkTagRelationshipID (with the bang)

or

rsMaxRTId.Fields("pkTagRelationshipID")

 

[boblarson]

Or you can also use rsMaxRTId("pkTagRelationshipID")

 

[Kryst51]

It works!!!!

Instead of using an insert into statement at the end I just set fkTagRelationshipID = MaxRTID. It seemed cleaner and I was getting some sort of runtime error having to do with a missing semicolon.

Here is my finished code!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Set MasterDbConn = CurrentProject.Connection
Dim rsMaxRTId As ADODB.Recordset
Dim rsMaxRTIDSQL As String
Dim MaxRTID As Integer
 
Set rsMaxRTId = New ADODB.Recordset
rsMaxRTIDSQL = "SELECT tblTagRelationships.pkTagRelationshipID" & _
               " FROM qryTagRelationshipMax" & _
               " INNER JOIN tblTagRelationships" & _
               " ON qryTagRelationshipMax.MaxOfDateTimeStamp" & _
               " = tblTagRelationships.DateTimeStamp;"
 
    With rsMaxRTId
        .Open rsMaxRTIDSQL, MasterDbConn, adOpenKeyset, adLockOptimistic, adCmdText
    MaxRTID = rsMaxRTId!pkTagRelationshipID
 
    Set rsMaxRTId = Nothing
    End With
 
Me.fkTagRelationshipID = MaxRTID
 
End Sub

THANK YOU SO MUCH BOB AND DCRAKE FOR ALL OF YOUR HELP AND PATIENCE! I couldn't have done this without you (clearly )

 

[LPurvis]

Hi

The semicolon is almost entirely optional. Odd that you perceived otherwise from an error. :-s
And, FWIW, though I'm a fan of closing and destroying object variables - I'd say that, of the two, closing is more important.
You opened the recordset, you close it. The old rule of thumb.

Out of interest, what process is this part of?
It might help offer suggestions if there's some context to it?
(Fetching a value in a BeforeUpdate event always, at least, raises an eyebrow.

Cheers.

 

[Kryst51]

Hi

The semicolon is almost entirely optional. Odd that you perceived otherwise from an error. :-s
And, FWIW, though I'm a fan of closing and destroying object variables - I'd say that, of the two, closing is more important.
You opened the recordset, you close it. The old rule of thumb.

Out of interest, what process is this part of?
It might help offer suggestions if there's some context to it?
(Fetching a value in a BeforeUpdate event always, at least, raises an eyebrow.

Cheers.

I am not sure what you mean by closing and destroying the object variable. I think I do close it, but then I need it for the other portion of code....

As far as what I am using this for Here it goes.

I am designing (re-designing) a database used for our nonconformances. there are so many different variations of what can happen that I decided it would give me a better looking interface, and better useability if I designed portions of it as semi- unbound forms. What I mean is this.

I have an NCR table, which has the master number for the file and is the number that is referred to in all correspondence concerning the issue. Then is a tag relationship table. This table ties together tags that are related, such as a ship tag number and return tag number.

On my form for entering a new NCR I have a pop-up form to add items to the NCR. this form is unbound and has some combo boxes which I will use later to find an item number and assign it to the tag relationship. Then I have a subform which is bound to the tag table. After the tag info is entered it then needs to be tied to a tag relationship (which is not created yet) and then that tag relationship needs to be tied to the NCR. Hence what I am doing with the code.

I am doing it this way as opposed to using the typical bound forms with relationships because of the nature of the data and how cumbersome it becomes to enter information in with the typical method. I have had a hard time explaining the purpose before in other threads I have asked questions in for this db. And I suppose this isn't the best explanation either. But FWIW there it is.

 

[LPurvis]

>> I am not sure what you mean by closing and destroying the object variable.

I should have been more specific.
You currently have
Set rsMaxRTId = Nothing
which is colloquially known as destroying (or releasing) the object variable.
Before that you would include the close method for objects which support it (we take it as read that the developers provided it for good reason ;-)
rsMaxRTId.Close

So you're using code to retrieve the master number of a parent record?
(It's hard to know just how the NCR and sub-Tags are related in your example without actual schema).
Is the key manually entered - as opposed to being a generated autonumber?
The datestamp is being used as the definitive measure of the "last" record. Again, how this is entered and how it relates to the data is of vital importance.

An autonumber can - and should - be fetched by other means than seeking the most recent entry (this is intensely important in a multi-user application).
A manually entered value can, surely, be grabbed and held in a variable for subsequent use?

I'm just raising the flag here that there are very likely options.

Cheers

 

[Kryst51]

I don't mind the flag, I appreciate it! Let me explain a little (actually a lot) more.

We have several tag types. A tag can be an "In House" tag, which means we are dealing with material already in our inventory. It can be a "Ship Tag" Meaning it has shipped to the customer and is not in our inventory. Or it can be a "Return Tag" which means it is returned by the customer and is back into our inventory.

I have a tag table, which holds the tag number, tag pieces, tag weight, and tag type (In House, Ship, or Return). I need to be able to relate these tags to one another as a return tag HAS to be traced back to its shipped tag, and to avoid having to enter that materials PO number, and vendor info multiple times. (Also for ISO purposes)

I accomplished this by creating a tag relationship table. This table stores the NCR ID, and the PO number, vendor info and some other info not important to the task at hand. This tagrelationship ID is stored as an fk in the tag table. Now a tag relationship can only have two tags (1Ship and 1Return) but it can only have one tag of the types in parens (1 In House or the Material has not been returned yet so there is no return tag).

An NCR can concern many POs all with the same item, and can be tedious to enter with a generic set up. i.e. form with NCR info, subform with tag relationship header, subsubform with tag info. Or can be many pos, many items, and on the customer side many combinations of invoices, tags, pos, etc.

My current DB goes like this:
NCR- Item(s)- Invoice(s)- Customer(s)- Credit(s)
\ - PO- Vendor(s)- Debit(s)

This causes a lot of tedious reentering of info for each item. (See attached screen shots)

So my thought was (As I know it can be done with VBA) to simplify the entering to give the people using it more simplicity. They don’t have to enter EVERY combination of invoice, item, and PO, just so that accurate reports can be built.

My code actually on this thread is not the whole code, just a portion…. Before this code, there is code that will assign the number, then right after that it will find that number and put it in the current record on my form. I thought about doing a subform with the tagrelationship, then a subform on that with the tag detail… But again, that just seemed too cumbersome. Am I wrong Do I just need to deal with it?


Edit: Oh, the date/time stamp is a default of "Now()" so that when a record is created it gets the stamp.... It's only purpose is so that I then find that record (with my code) to be put in the tags table...