How to confirm value of field a based on value of field b?

[PartsPig]

I am trying to confirm if a work order (RONum) is still in Open status (IsOpen). My table has may lines for each work order but they should all have the same value for IsOpen. I am trying to find one line item with a match to RONum and return the value of IsOpen. Below is the code I started with but I am getting an error "Object Required." I am using a simple form to test the functionality but will then use the solution in another more complicated form. Any assistance will be appreciated.

Private Sub Go_Click()
    Dim strRO As String
    Dim blnIsOpen As Boolean
    
    blnIsOpen = True
    strRO = txtRONum
    'MsgBox "strRO = " & strRO, vbOKOnly
    
    If tblPartsTracking.RONum = strRO And tblPartsTracking.IsOpen = True Then
        MsgBox "RO " & strRO & " is open.", vbOKOnly
    Else
        MsgBox "RO " & strRO & " is closed.", vbOKOnly
    End If

End Sub

 

[XPS35]

I think you might have a design problem here. In a properly designed database each work order should only be stored once. In that case it is not possible to have multiple values for IsOpen. So review your design and you won't need any code.

 

[jackd]

Can you show us your tables and relationships? Relationships view.

Also put Option Explicit as second line in your module.

 

[Micron]

... and learn to use the immediate window to inspect references. Name is a property of just about every object, so you can test your references by using it. Type:
? tblPartsTracking.Name and hit Enter. If you get an error, then you know your code has no idea what that is.

 

[welshgasman]

Is tblPartsTracking.RONum a form name? If so, a lousy formname
If so you would use the Forms collection.
If a table, then you would use a DlookUp I belive, but not sure how todetermine a table is open or need to?, that is why I thought it might be a form?

Also you should check it is open before trying to check a control on it. So swap those over.

 

[PartsPig]

tblPartsTracking.RONum - The table I am searching through is tblPartsTracking and the "job" number I am searching for is field RONum (repair order number). There are only 2 tables in this DB, tblPartsTracking with lots of information (including job number RONum and whether open or closed with a checkbox IsOpen) and tblTechnicians with a simple list of names and nothing else.

There can be many different parts for a single job but the job will only have one ID (eg: 8405). Either the job is open and still being worked on or it is has been completed and closed. I see now that maybe I should have created a tblJob to have job numbers and open status but I don't. If the job is open the field IsOpen will have a check in the checkbox, if it is closed it will not have the check.

I have a form to close jobs once they are complete and all it has is a txtbox to input the job number and a Close button. It works perfectly using the code below...

mySQL = "UPDATE tblPartsTracking " & _
                "SET tblPartsTracking.IsOpen = False " & _
                "WHERE ((tblPartsTracking.RONum)=" & strRO & ");"
        DoCmd.RunSQL mySQL
        MsgBox "RO " & strRO & " has been closed.", vbOKOnly

I would like to add some lines to first check if the job is closed before running the UPDATE which brings me to where we are.

XPS35, Thanks but I have no desire to redesign the DB even though it has not been designed properly. It is working fine for what we need and has no potential for growth. I will likely address this at another time but for now it is good.

jackd, Thanks for the Option Explicit advice. I will definitely add that line. Please see the table names and relationship mentioned above.

Micron, Thanks for reminding me of the immediate window and how to use it for testing. Like I said in one of my many posts here, I have been out of the game for quite some time and feel like a beginner again, LOL.

welshgasman, That would be a lousy name for a form! LOL! But it is actually for one of the two tables in the DB. I will check out Dlookup and see what that does.

 

[Micron]

99.9% of the time, each entity should have its own table. Your entities seem to be
- parts
- jobs
- status
- techs
and maybe others such as db users. However, you have stated that you have no desire to make it so. In the end, I don't see why that code approach when you can open a query and if it returns any records based on Open (or whatever) then you have your answer. That might also make it more flexible in that you could use other status' if that is ever needed. DLookup could work . It might look something like

If DLookup("IsOpen","tblPartsTracking","ROnum = " & Me.txtROnum " And IsOpen = 'Open'") > 0 Then

However, if you have a working sql statement where ROnum = something and IsOpen = "Open" then I'd open a query on that and check its record count (in code).

I'm curious as to why you get the error though, so if you care to post a file copy in some file share I'd probably make the time to look.

 

[PartsPig]

99.9% of the time, each entity should have its own table. Your entities seem to be
- parts
- jobs
- status
- techs
and maybe others such as db users. However, you have stated that you have no desire to make it so. In the end, I don't see why that code approach when you can open a query and if it returns any records based on Open (or whatever) then you have your answer. That might also make it more flexible in that you could use other status' if that is ever needed. DLookup could work . It might look something like

If DLookup("IsOpen","tblPartsTracking","ROnum = " & Me.txtROnum " And IsOpen = 'Open'") > 0 Then

However, if you have a working sql statement where ROnum = something and IsOpen = "Open" then I'd open a query on that and check its record count (in code).

I'm curious as to why you get the error though, so if you care to post a file copy in some file share I'd probably make the time to look.

Which file share do you recommend?

 

[Micron]

I should have noticed this before, but the reason you'll get that error is because you cannot refer to a table or any of its fields that way. You could if you included a reference to the tables collection; e.g. currentdb.TableDefs("tblpartstracking") but you cannot refer to its data that way. Which of the many records should Access be looking at? I recently suggested DCount for a similar task in some other thread so maybe try this

    If DCount("IsOpen", "tblPartsTracking", "ROnum = 8423 And IsOpen = true") > 0 Then
        MsgBox "RO " & strRO & " is open.", vbOKOnly
    Else
        MsgBox "RO " & strRO & " is closed.", vbOKOnly
    End If

There may be issues with that, depending on what you need. You will get a value of True if there is just one record that satisfies the test, even if other records for the same ROnum are not open.

 

[PartsPig]

Works perfectly!!! Thanks!!