Solved Open a record in one of 2 forms based on the last character of a field

C

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
Hello,

I have a button that opens a form based on the jobID
e.g.
1637638163752.png


Obviously all works, however, I want to add an additional option to this open form/record button.
Criteria is:
Look at the Job# (JobNumber) field, If the JobNumber ends with an R
e.g.
1637638323982.png

then open the record with the form "ReworkF"

Otherwise open the record with the form "JobDetailF"

Code: 
'------------------------------------------------------------
' Open Matching Job Details or Rework Form
'
'------------------------------------------------------------
Private Sub BtnOpenJob_Click()
    On Error GoTo Err_BtnOpenJob_Click
    Dim stDocName As String
    Dim stLinkCriteria As String

' (Need to add this option)
        ' stDocName = "ReworkF" ' Open this form at linked record if JobNumber ends with "R"
    
    stDocName = "JobDetailF" ' Otherwise open this form
    
    stLinkCriteria = "[JobID]=" & Me![JobID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_BtnOpenJob_Click:
    Exit Sub

Err_BtnOpenJob_Click:
    MsgBox err.Description
    Resume Exit_BtnOpenJob_Click
    
End Sub

Thank you :)
 
You can use the VBA.Right() function to determine the rightmost character(s) of a string.
Code: 
If VBA.Right(Me.JobID, 1) = "R" Then MsgBox "Open the other form here!!!"
Cheers,
 
you can also use "Like" operator:
Code: 
'------------------------------------------------------------
' Open Matching Job Details or Rework Form
'
'------------------------------------------------------------
Private Sub BtnOpenJob_Click()
    On Error GoTo Err_BtnOpenJob_Click
    Dim stDocName As String
    Dim stLinkCriteria As String

' (Need to add this option)
    If Me![JobID] & "" Like "*R" Then
       stDocName = "ReworkF" ' Open this form at linked record if JobNumber ends with "R"
    Else
       stDocName = "JobDetailF" ' Otherwise open this form
    End If
    
    stLinkCriteria = "[JobID]=" & Me![JobID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_BtnOpenJob_Click:
    Exit Sub

Err_BtnOpenJob_Click:
    MsgBox err.Description
    Resume Exit_BtnOpenJob_Click
    
End Sub
 
MarkK said:
You can use the VBA.Right() function to determine the rightmost character(s) of a string.
Code: 
If VBA.Right(Me.JobID, 1) = "R" Then MsgBox "Open the other form here!!!"
Cheers,
Click to expand...
G'day Mate,
Thank you for the reply.

I have done the code as per below. The form opens but the form is blank.

Code: 
Private Sub BtnOpenJob_Click()
    On Error GoTo Err_BtnOpenJob_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    
        If VBA.Right(Me.JobNumber, 1) = "R" Then
            stDocName = "ReworkF" ' Open this form at linked record if JobNumber ends with "R"
            stLinkCriteria = "[JobID]=" & Me![JobID]
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else
            stDocName = "JobDetailF" ' Otherwise open this form
            stLinkCriteria = "[JobID]=" & Me![JobID]
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
    
Exit_BtnOpenJob_Click:
    Exit Sub

Err_BtnOpenJob_Click:
    MsgBox err.Description
    Resume Exit_BtnOpenJob_Click
    
End Sub
 
arnelgp said:
you can also use "Like" operator:
Code: 
'------------------------------------------------------------
' Open Matching Job Details or Rework Form
'
'------------------------------------------------------------
Private Sub BtnOpenJob_Click()
    On Error GoTo Err_BtnOpenJob_Click
    Dim stDocName As String
    Dim stLinkCriteria As String

' (Need to add this option)
    If Me![JobID] & "" Like "*R" Then
       stDocName = "ReworkF" ' Open this form at linked record if JobNumber ends with "R"
    Else
       stDocName = "JobDetailF" ' Otherwise open this form
    End If
   
    stLinkCriteria = "[JobID]=" & Me![JobID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_BtnOpenJob_Click:
    Exit Sub

Err_BtnOpenJob_Click:
    MsgBox err.Description
    Resume Exit_BtnOpenJob_Click
   
End Sub
Click to expand...
Thank you,

The difference is the JobID is not where the "R" is.
Its in a field JobNumber.

I am getting a blank form at the moment.

thank you
 
do you have the JobID on your recordsource of your form?
or do you have [Entry ID] instead?
 
you check the RowSource of your Forms again (especially if it is a Query).
check if JobID is included (and is not Renamed).
 
Chief said:
G'day Mate,
Thank you for the reply.

I have done the code as per below. The form opens but the form is blank.

Code: 
Private Sub BtnOpenJob_Click()
    On Error GoTo Err_BtnOpenJob_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
   
        If VBA.Right(Me.JobNumber, 1) = "R" Then
            stDocName = "ReworkF" ' Open this form at linked record if JobNumber ends with "R"
            stLinkCriteria = "[JobID]=" & Me![JobID]
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else
            stDocName = "JobDetailF" ' Otherwise open this form
            stLinkCriteria = "[JobID]=" & Me![JobID]
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
   
Exit_BtnOpenJob_Click:
    Exit Sub

Err_BtnOpenJob_Click:
    MsgBox err.Description
    Resume Exit_BtnOpenJob_Click
   
End Sub
Click to expand...
You are repeating code, never a good idea. :-(
Look how arnelgp wrote his code.
 
stLinkCriteria = "[JobID]=" & Me![JobID] The second [JobID] refers to the control name (on the form), not the field name (in the bound table). And maybe just check to see if the form's Data Entry property is not set to "Yes".
Cheers,
 
arnelgp said:
you check the RowSource of your Forms again (especially if it is a Query).
check if JobID is included (and is not Renamed).
Click to expand...
Morning,
My Form "ReworkF" - Record Source is the Table "JobInfoT"
I have gone thru my fields and ensured nameing and control source is correct.
On both forms (Where the open button is) Field is "JobID" and the ReworkF the field is "JobID"
1637703354613.png
1637703389457.png


Code for the button is
Code: 
'------------------------------------------------------------
' Open Matching Job Details or Rework Form
'
'------------------------------------------------------------
Private Sub BtnOpenJob_Click()
    On Error GoTo Err_BtnOpenJob_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
   
        If VBA.Right(Me.JobNumber, 1) = "R" Or VBA.Right(Me.JobNumber, 1) = "S" Then
            stDocName = "ReworkF" ' Open this form at linked record if JobNumber ends with "R or "S""
        Else
            stDocName = "JobDetailF" ' Otherwise open this form
        End If
       
    stLinkCriteria = "[JobID]=" & Me![JobID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
   
Exit_BtnOpenJob_Click:
    Exit Sub

Err_BtnOpenJob_Click:
    MsgBox err.Description
    Resume Exit_BtnOpenJob_Click
   
End Sub

Open a standard job and the record and details opens correctly.
However when I try to open a rework or shortage job, it is blank and the JobID is (New)
1637703557639.png


Appreciate the help.

Also in properties the filter is correct and showing the corrosponding JobID that I selected.
1637703709338.png
 
As I said in post # 10 check that you are not opening the form in data entry mode, what you actually are according to the black screen shot above. You need to set the Data Entry property to No.

Cheers,
 
bastanu said:
As I said in post # 10 check that you are not opening the form in data entry mode, what you actually are according to the black screen shot above. You need to set the Data Entry property to No.

Cheers,
Click to expand...
G'Day mate,
Sorry I missed that and didn't check the form.

Thank you!!! That worked and all is good in the world again:)

Thank you everyone!
 
It is poor practice to mush attributes together into a single field. Instead of adding an R to the end of the JobID which should be an autonumber, best practice would be to add a new column and use it to indicate the type of job.

Also, if most of the fields are the same, then it is also poor practice to have more than one edit form because that requires you to duplicate your validation code. It is probably best to use a single form for both types and add additional code if necessary for the rework jobs
 
You must log in or register to reply here.

Similar threads

M
Open Unbound form and filter subform
Replies
7
Views
310
Gasman
Gasman
J
code to close my current form on pressing the button to open my Main Menu
2
Replies
20
Views
757
Edgar_
E
S
Should my code embrace more of the "Single Responsibility Principle"?
Replies
6
Views
495
jwcolby54
jwcolby54
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
644
CJ_London
C
A
Filter Records
Replies
1
Views
268
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom