Recordset problems

[Dinger_80]

I am having a problem with retrieving the information I am looking for. Basically I am checking to see if a user has selected a TRNumber from a drop down menu and if that TRNumber matches any other records in the table in that field. If it does, is that Number already associated with the user selecting it. If it is I want to get the ID number so I can simply update the record with new information. Here is what I have so far.

Dim Rs As DAO.Recordset
Dim Tech As String
Dim MsgStr As String
Dim IdMod As String
Set Rs = CurrentDb.OpenRecordset("PrepStartDateBlankQuery")
Tech = DLookup("ID", "UserReportsComboQuery")
MsgStr = "Another Technician has put a start date on this test request already. Do you want to update the test request with your information?"
If DCount("TRNumber", "PrepStartDateBlankQuery", "[TRNumber] ='" & Me.TRNCombo.Column(1) & "'") = 0 Then
    MsgBox "ugg"
    Exit Sub
    'Use update query here
Else
    If Not (Rs.EOF And Rs.BOF) Then
        Rs.MoveFirst
        With Rs
            .FindFirst "TRNumber = '" & Me.TRNCombo.Column(1) & "'" And "TestTechnician = " & Tech
            .MoveNext
        End With
        DoCmd.OpenQuery "UpdatePrepStartDateQuery"
        Exit Sub
    Else

 

[vbaInet]

I'm guessing the table source of the two queries you're using in the DLookup() function and the Recordset is the same?

 

[Dinger_80]

PrepStartDateBlankQuery is based on a table called FinishedTestRequestsTable and UserReportsComboQuery is also based on the same table, but I has the related table of BuisnessNameEmployeeTable.

 

[vbaInet]

You only need the Recordset part of the code, all the other bits are redundant. Look into the NoMatch property of a recordset.

 

[Dinger_80]

I understand that the nomatch would show if nothing shows up for the recordset but I am acutally looking for two different things. The fist part where I have the DCount is to see if any TRNumbers match the drop down menu. If there is I want the next query run but to see if any records returned match both the TRNumber and TestTechnician. This is to see if the Technician has already started work on the TRNumber they have selected. My issue is trying to get the recordset to look for both the TRNumber and TestTechnician.

 

[vbaInet]

That's why I'm saying you only need a recordset. The first check can be done with the same recordset and the second with the same recordset as well, plus the update will be done in (again) the same recordset.

By the way, your DLookup() what is it doing there? Does the query only contain one record?

 

[Dinger_80]

Ok I think I understand what you mean about the DCount and Recordset being redundant. May take some working with to fully understand. The DLook up used for the Tech at that point may or may not return any records. That is what I am trying to assertain. Either way I am still having a problem with getting the recordset to go to the record I am looking for. I have intentionally set things up so as to be able to test the code.

 

[vbaInet]

The value returned by the DLookup drives the search so that's an important part. Do you realise that without any criteria it will return the first record in your query? This doesn't seem to relate to anything.

 

[Dinger_80]

Yes the Tech DLookup will only ever return one record. You know what I think I was over thinking this whole section. If I generate a query that only returns TRNumbers that are related to that user, then use the combo box as the criteria to see if that number matches any of the results. That would give me the results I am looking for much easier.

SELECT FinishedTestRequestTable.TRNumber, CrouseHindsPersonalTable.LoginID
FROM FinishedTestRequestTable INNER JOIN CrouseHindsPersonalTable ON FinishedTestRequestTable.TestTechnician = CrouseHindsPersonalTable.ID
WHERE (((CrouseHindsPersonalTable.LoginID)=GetUserName()));

Would be the SQL I would use and here is the code I would put in.

If DCount("TRNumber", "PrepStartDateBlankQuery", "[TRNumber] ='" & Me.TRNCombo.Column(1) & "'") = 0 Then
    'Use update query here
    Exit Sub
Else
    If Not IsNull('Query field and name here, and criteria based on combobox here) Then
        If MsgBox("You have already started work on this Test Request Number. Are you updating the record?", vbYesNo + vbQuestion, "Update?") = vbNo Then
            Me.Undo
            DoCmd.Close
        Else
            'Update record here

Haven't tested this yet busy doing my actual job, but I think this should work.

 

[vbaInet]

Give it a try and let us know.

 

[Dinger_80]

Ok finally got a chance to implement the changes I needed to get the results I wanted. Here is the final code, mind you that I went around the need for a recordset by building a better query.

Tech = DLookup("ID", "UserNameQuery")
MsgStr = "Another Technician has put a start date on this test request already. Do you want to update the test request with your information?"
If DCount("TRNumber", "PrepStartDateBlankQuery") = 0 Then
    Me.Refresh
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateTestRequestRIDQuery"
    DoCmd.SetWarnings True
Else
    If Not IsNull(DLookup("TRNumber", "PrepStartDateBlankQuery", "[TestTechnician] = " & Tech)) Then
        If MsgBox("You have already started work on this Test Request Number. Are you updating the record?", vbYesNo + vbQuestion, "Update?") = vbNo Then
            Me.Undo
            DoCmd.Close
        Else
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "UpdatePrepStartDateQuery"
            DoCmd.SetWarnings True
        End If
    Else
        If Me.PrepStartDate < DLookup("PrepStartDate", "PrepStartDateBlankQuery") Then
            If MsgBox(MsgStr, vbQuestion + vbYesNo, "Continue?") = vbYes Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "UpdateTestRequestRIDQuery"
                DoCmd.SetWarnings True
            Else
                If MsgBox("Another Technician has already started work on this. Do you want to continue?", vbQuestion + vbYesNo, "Continue?") = vbYes Then
                    Me.Refresh
                Else
                    DoCmd.Close
                End If
            End If
        End If
    End If
End If

 

[vbaInet]

I'm glad that you got rid of the Recordset code completely.

To avoid turning on and off the warnings and for better traceability you can run an action query using this method:

Dim db As DAO.Database

Set db = CurrentDb

db.Execute "[COLOR="Blue"]NameOfQuery[/COLOR]", dbFailOnError

But on quick glance I think it's a little bit over engineered. When I get a moment, I'll re-write it and send.

 

[vbaInet]

Ok here's what I came up with based on what you wrote:

    Dim db              As DAO.Database
    Dim blUpdate        As Boolean
    Dim blClose         As Boolean
    Dim intResponse     As Integer
    Const PREP_QUERY    As String = "PrepStartDateBlankQuery"
    
    Tech = [COLOR="Red"]DLookup("ID", "UserNameQuery")[/COLOR]
    
    If DCount("TRNumber", PREP_QUERY, "[TestTechnician] = " & Tech) > 0 Then
        intResponse = MsgBox("You have already started work on this Test Request Number." & _
                             vbNewLine & vbNewLine & _
                             "Are you updating the record?", _
                             vbYesNo + vbQuestion, _
                             "Update?")
        If intResponse = vbYes Then
            blUpdate = True
        Else
            blClose = True
        End If
    ElseIf Me.PrepStartDate < [COLOR="Red"]DLookup("PrepStartDate", PREP_QUERY)[/COLOR] Then
        MsgStr = "Another Technician has already started work on this." & _
                 vbNewLine & vbNewLine & _
                 "Do you want to update the test request with your information?" & _
                 vbNewLine & vbNewLine & _
                 "Yes: Update test request with your information" & vbNewLine & _
                 "No: Continue working on test request" & _
                 "Cancel: Cancel update"
        
        intResponse = MsgBox(MsgStr, vbQuestion + vbYesNoCancel, "Update or Continue?")
        
        If intResponse = vbYes Then
            blUpdate = True
        ElseIf intResponse = vbNo Then
            [COLOR="red"]Me.Refresh[/COLOR]
        Else
            blClose = True
        End If
    Else
        blUpdate = True
    End If
    
    If blClose Then
        Me.Undo
        DoCmd.Close acForm, Me.Name
    ElseIf blUpdate Then
        db.Execute "UpdateTestRequestRIDQuery", dbFailOnError
    End If

I'm still going to question your use of the following lines of code highlighted in red:
1. the DLookup function in two instances. There's no criteria so you're most likely not returning the right value. Why's this?
2. Me.Refresh. What's the point of this?

And one last thing, I'm hoping that you've declared Tech as a String variable somewhere?

 

[Dinger_80]

Yes all of my variables are defined. I am no where near great with programming and wouldn’t be surprised if I am over engineering a lot of stuff. I did change out Tech to be Tech = Me.TestTechnician a field that is on the form I have. I use a lot of queries because I am still learning SQL and how to make queries return the values I am looking for. As I grow I am working on doing things better by having a simple query and using criteria after it to give me what I need. Let me ask, how does one achieve multiple bits of information from a query, without doing multiple Dlookups? I understand that a loop of some sort would be required, but at my level couldn’t even dream of what that may look like.

That being said I can understand at least in theory what is going on. I understand the declared variables and what they represent. I have some questions later on though. I also wasn’t aware of a way to shift the lines of a string in a message box. Not that I was ever looking to but it certainly give me something to think about. So my question about the declared variables is this, when you tell blClose and blUpdate to be true it goes to the code at the bottom of the screen and runs that code then resumes where it left off? If that is the case that is a huge eye opener and could do a lot with that. Usually when I have repeat code I have generated a Public Sub on the same form. I don’t do a lot of that but a few times.

Sadly the Me.Refresh , I am sure is going to make you feel sick, is my backwards way of saving information. I have no understanding for why DoCmd.RunCommand acCmdSave or some slight variation of that caused me so much grief. Someone may go to do something and an error occurs. My error handler picked it up. They try again and the system would say something about no record being there or on file. It would create more errors. The way I did it worked seamlessly so after a few weeks of the Docmd way I swapped it back until such a time as I could resolve the issue at hand.

I can’t thank you enough for all of the wisdom you parted onto the world and myself.

 

[vbaInet]

I did change out Tech to be Tech = Me.TestTechnician a field that is on the form I have. I use a lot of queries because I am still learning SQL and how to make queries return the values I am looking for.

If the tech existed on the form already you didn't need to fetch it from anywhere else. Good you changed that. And queries will give better performance than a recordset when it comes to inserting, updating and deleting information, so wherever possible use queries.

I'm still curious about the second DLookup()? I've asked this question a couple of times but still had no response. Why does it not have any criteria?

Let me ask, how does one achieve multiple bits of information from a query, without doing multiple Dlookups? I understand that a loop of some sort would be required, but at my level couldn’t even dream of what that may look like.

If you want one record but two or more fields returned you can still use one DLookup(). There's a way to handle this.
If you want multiple records you use a recordset or even a listbox/combo box.

I also wasn’t aware of a way to shift the lines of a string in a message box.

Yes it's just the underscore that does it. If it's a continuing string then you obviously need to concatenate before you put the underscore.

So my question about the declared variables is this, when you tell blClose and blUpdate to be true it goes to the code at the bottom of the screen and runs that code then resumes where it left off?

It doesn't actually go anywhere. Code will still run from top to bottom. Those variables are there to tidy things up a bit by grouping similar actions together.

Sadly the Me.Refresh , I am sure is going to make you feel sick, is my backwards way of saving information. I have no understanding for why DoCmd.RunCommand acCmdSave or some slight variation of that caused me so much grief.

That's what we're here for Dinger, if there's something you don't know, just ask and someone will guide you.
So what you need is:

If Me.Dirty Then Me.Dirty = False

 

[Dinger_80]

I guess to try and answer your question about the Dlookup which I guess I have poorly tried to answer is that I am merely getting multiple values from the same record. The reason I have no criteria is because the query returns only one record. Sadly I have become very good at reinventing the wheel. Only through errors have I discovered wisdom parted on me that I couldn't comprehend at the time. So I have a lot of similar queries because I didn't use criteria on them. I am still working on building better queries, I am working more on understanding inner and outer joins to help return values I need. I hope this answers your question better about the second Dlookup.
I am curious as to how to return more than one value of a Dlookup when all I can find points to the fact only one field maybe returned. I use a lot of Dlookups because I send a lot of emails, use unbound text boxes so users can see useful information on forms.
I only use record sets for two items in my system. One for gathering multiple email addresses for people and the other for outputting equipment used for testing.

 

[vbaInet]

As long as the query returns 1 record that's fine, but this is why I brought up this question:

DLookup("TRNumber", "PrepStartDateBlankQuery", [COLOR="Red"]"[TestTechnician] = " & Tech[/COLOR])

... as you can see the criteria is not relevant because you could have returned the TestTechnician value from this one record query and check it against Tech.

As for returning multiple records in a DLookup it's possible but it's just not worth the effort because (as I already mentioned) you can use a Recordset, combo box, list box or subform. With recordsets you use FindFirst, FindNext, Filter and NoMatch methods/properties.

 

[Dinger_80]

Ok I understand now what you are saying about that Dlookup and totally agree.
Maybe my other question was poorly stated, as I don't usually have multiple records, how would I return multiple files from a single record using a single Dlookup as you mentioned is possible earlier?

 

[vbaInet]

That was why I asked before if you wanted multiple fields (i.e. columns) or multiple records (i.e. rows). I did think you meant fields but since you stated otherwise I went with it

Here's a way:

Dim varAll As Variant
Dim strTestTech As String
Dim dtePrepDate As Date

varAll = DLookup("[TestTechnician] & Chr(0) & [PrepStartDate]", "PrepStartDateBlankQuery")
varAll = Split(varAll, Chr(0))

strTestTech = varAll(0)
dtePrepDate = CDate(varAll(1))

The only problem with this is that it doesn't preserve the data types used and it won't handle Null. The DLookup returns both fields as one concatenated string.

 

[Dinger_80]

I can't thank you enough for all of your help. I feel like I have taken a huge step forward. I had plans of reorganizing my code and optimizing my queries. This will make my improvements that much better. I am looking forward to implementing all I have learned.