Solved Open a record in one of 2 forms based on the last character of a field (1 Viewer)

C

Chief

Registered User.
Local time
Today, 11:48
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 :)
 
MarkK

MarkK

bit cruncher
Local time
Today, 11:48
Joined
Mar 17, 2004
Messages
8,179
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,
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:48
Joined
May 7, 2009
Messages
19,227
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
 
C

Chief

Registered User.
Local time
Today, 11:48
Joined
Feb 22, 2012
Messages
156
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
 
C

Chief

Registered User.
Local time
Today, 11:48
Joined
Feb 22, 2012
Messages
156
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
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:48
Joined
May 7, 2009
Messages
19,227
do you have the JobID on your recordsource of your form?
or do you have [Entry ID] instead?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:48
Joined
May 7, 2009
Messages
19,227
you check the RowSource of your Forms again (especially if it is a Query).
check if JobID is included (and is not Renamed).
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 19:48
Joined
Sep 21, 2011
Messages
14,217
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.
 
bastanu

bastanu

AWF VIP
Local time
Today, 11:48
Joined
Apr 13, 2010
Messages
1,402
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,
 
C

Chief

Registered User.
Local time
Today, 11:48
Joined
Feb 22, 2012
Messages
156
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
 
bastanu

bastanu

AWF VIP
Local time
Today, 11:48
Joined
Apr 13, 2010
Messages
1,402
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,
 
C

Chief

Registered User.
Local time
Today, 11:48
Joined
Feb 22, 2012
Messages
156
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!
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 19, 2002
Messages
43,203
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
Populate Sub Form with similar to last main form record data
Replies
6
Views
515
MHART08
M
R
  • Question
Save the names of tables, forms, and reports in a Microsoft Access database to a table named "ObjectNames.
2
Replies
32
Views
885
spaLOGICng
S
L
Second instance of Word opened from Access arises security warning?
Replies
14
Views
640
lauro
L
C
Next record code moves to the next record in two sets of records in two textboxes when it should be only be moving down on one
Replies
3
Views
262
ChazRab
C
C
Next record button to move to the next record in a textbox
2
Replies
26
Views
833
ChazRab
C

Users who are viewing this thread

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