Rewriting a VB Statement

[KristenD]

I currently have a VB statement written for a pop up form based on a main form. I would like to change it so it is based off of a subform and somehow tie those tables together. I'm not sure how that would work seeing as I'd have to go back and match up everything since July when I modified the databse to include the data.

This is the code I am currently working with:

Private Sub EmploymentStatus_AfterUpdate()
If Me.EmploymentStatus = "Active" Then
    Me.tblEmpInfo.Visible = True
    
Else
    Me.tblEmpInfo.Visible = False
    
End If
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
    Me.tblEmpInfo.Visible = True
    
Else
    If Me.EmploymentStatus = "Active" Then
        Me.tblEmpInfo.Visible = True
    Else
        Me.tblEmpInfo.Visible = False
    End If
End If

I would like to base it off of Me.Status (the name of the field in a subform based off of a completely different table (tblEmpRating)) and would like to pop up if any one of these terms in the combo box is used "New Hire", "Rehire", "Transfer".

So I changed the VB statement to read:

Private Sub Status_AfterUpdate()
If Me.Status = "New Hire", "Rehire", "Transfer" Then
    Me.tblEmpInfo.Visible = True
    
Else
    Me.tblEmpInfo.Visible = False
    
End If
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
    Me.tblEmpInfo.Visible = True
    
Else
    If Me.Status = "New Hire", "Rehire", "Transfer" Then
        Me.tblEmpInfo.Visible = True
    Else
        Me.tblEmpInfo.Visible = False
    End If
End If

Now it is telling me I have a complile or GoTo error. Am I wording it wrong? Thank you for the help!!

 

[AccessMSSQL]

Assuming that your main form is opened that has the subform in it opened you just need to refer to me.Status as this:

Forms!frmMainForm!subFormName.Form!Status

 

[AccessMSSQL]

Also, you can't create a subprocedure in a main form for an event on a control in a subform. It looks like you have subprocedure for Status after update in your main forms code module.

 

[KristenD]

Would it make a difference if the subform would have a new record each and every time? There would be no updating the subform as each time that status change occurs it would be a new record. So I believe I should get rid of the first part of the code, correct?

Private Sub EmploymentStatus_AfterUpdate()
If Me.EmploymentStatus = "Active" Then
    Me.tblEmpInfo.Visible = True
    
Else
    Me.tblEmpInfo.Visible = False
    
End If
End Sub

This part should be irrelevant as there would be no updating occurring.

 

[AccessMSSQL]

Can you explain more about your project? What are you trying to accomplish with this code? It looks like you are just trying to hide the subform - assuming your subform is tblEmpInfo? (as a matter of practice you should try to name form objects differntly than table objects. if tblEmpInfo is a subform I would name it fsubEmpInfo or something to distinguish that it is a form). Also please explain your tables? Are you saying you have data in the main employee table but now you are trying to populate the new table. I guess I can look at your project.

 

[JHB]

The problem with the "Compile error" is this line.

[FONT=&quot]If Me.Status = "New Hire", "Rehire", "Transfer" Then[/FONT]

Correct is:

[FONT=&quot]If Me.Status = "New Hire" Or [/FONT][FONT=&quot]Me.Status = [/FONT][FONT=&quot] "Rehire" Or [/FONT][FONT=&quot]Me.Status = [/FONT][FONT=&quot] "Transfer" Then[/FONT]

 

[KristenD]

This is an employee tracking database and it's main components are to track employee ratings (skills), OSHA certification, and eventually when I can get it to work correctly add in licensing (employees in certain states must hold the correct licenses in order to be on certain jobs) to track the state and which level license the employee holds, and hopefully other certifications that the employee holds.

Basically what I am trying to do now is tie together information (employee rating with the job they are currently at) and I am struggling to figure out how exactly to get that information to tie together.

This is an Access 2007 database and I have attached the zipped version for you to look at.

 

[AccessMSSQL]

Okay. I think I understand what you are trying to do. You are just trying to evaluate the value of the StatusUpdate drop down in your Skill Assessment subform, right? If status is a certain value then you want to show or hide the tblEmpInfo Form.

Here is what you need to do: change your on current of the main form

Private Sub Form_Current()
If Me.NewRecord Then
    Me.tblEmpInfo.Visible = True
Else
    If Me.EmploymentStatus = "Active" Then
        Me.tblEmpInfo.Visible = True
    Else
        Me.tblEmpInfo.Visible = False
    End If
End If
Select Case Forms!EmployeeEntry!tblEmpRating.Form!StatusChange
    Case "New Hire", "Rehire", "Transfer"
        Me.tblEmpInfo.Visible = True
    Case Else
        Me.tblEmpInfo.Visible = False
End Select
    
End Sub

this works.

 

[KristenD]

Yes, I want to be able to somehow tie together the information for the jobs and the status changes. As of right now when I run queries the information does not match up.

I could not get that to work in my database. The pop up form does not show up at all. Am I doing something wrong?

 

[AccessMSSQL]

Hi Kristen:
Sorry I got busy with other projects and never got back to this thread. Were you able to get this working??