Opening a form for specified control 2

[jdwm2310]

I have a problem I created an Employee form and on the form I have a preview enrollment button that would open either forms (1) SESP Enrollment- Above 170K or (2) SESP Enrollment-Below 170K. These enrollment forms are regulated by a text box that requires a number input. If it's 150K then the form Below 170K will open up, etc.

My problem is that once I click on the Preview Enrollment button I want to view the form for the specified individual. So a person who does not have an enrollment form a blank form should be shown. If someone filled out the enrollment form than the preview button will illustrate that person's enrollment form. This is the coding I have which is not working ..

If Me![SESP Enrollment] > 170 Then
DoCmd.OpenForm "SESP Enrollment (Above 170K)"
DoCmd.GoToRecord , , acGoTo
Else
DoCmd.OpenForm "SESP Enrollment (Below 170K)"
DoCmd.GoToRecord , , acGoTo
End If

If Me![SESP Enrollment] > 170 Then
DoCmd.OpenForm "SESP Enrollment (Above 170K)"
DoCmd.GoToRecord , , acGoTo
Else
DoCmd.OpenForm "SESP Enrollment (Below 170K)"
DoCmd.GoToRecord , , acGoTo
End If

DoCmd.OpenForm "SESP Enrollment (Above 170K)" Or "SESP Enrollment (Below 170K)", acPreview, [Employee Information]

Any suggestions????

 

[Dustman]

Well.. there are a few ways of doing that, depending on how your forms are linked. Try this..

If Me![SESP Enrollment] > 170 Then
  DoCmd.OpenForm "SESP Enrollment (Above 170K)",,, "[EmployeeID] = " & Me!EmployeeID, acEdit
Else
  DoCmd.OpenForm "SESP Enrollment (Below 170K)",,, "[EmployeeID] = " & Me!EmployeeID, acEdit
End If

The key thing will be linking the forms. Substitute [EmployeeID] with your primary key that will link the two forms and then substitute the Me!EmployeeID with the control name that holds the primary key on the Employee Form.

That should work.. If it doesn't then there are some other things you can do to make it work. This will only open the form for editing right now.. I didn't include any code to check for a blank record. Tell me if this one works and I can help you write the other code.
-Dustin
Rom 8:28

 

[jdwm2310]

Dustman,

Employee Information - is the control source.

If Me![SESP Enrollment] > 170 Then
DoCmd.OpenForm "SESP Enrollment (Above 170K)", , , "[Employee Information] = " & Me![Employee Information], acEdit
Else
DoCmd.OpenForm "SESP Enrollment (Below 170K)", , , "[Employee Information] = " & Me![Employee Information], acEdit
End If

I get a syntax error : (comma) in query expression "[Employee Info]=Bach, Andrew F..." When debugged the second DoCmd. operation is highlighted...Any suggestions

 

[Dustman]

I've had this problem before.. anytime there is a commah or an apostrophe, VB will think it is part of your code. You have to enclose the whole string in single quotes.

If Me![SESP Enrollment] > 170 Then
  DoCmd.OpenForm "SESP Enrollment (Above 170K)", , , "[Employee Information] =

'

" & Me![Employee Information]

& "'"

, acEdit
Else
  DoCmd.OpenForm "SESP Enrollment (Below 170K)", , , "[Employee Information] =

'

" & Me![Employee Information]

& "'"

, acEdit
End If

I think that will fix it.. -Dustin
Rom 8:28

 

[jdwm2310]

Dustman,

It worked!!!!!!!!! And when I changed the acEdit to acReadOnly a blank screen pops (just what I wanted) Thanks B-)

 

[Dustman]

Cool.. just remember that your names can't have an apostraphe either.. that really messes things up in SQL. If there is a chance of ever having one, you have to make you a function that looks for them and adds a second one to it. IE: Dustman's would need to be changed to Dustman''s.

Also, if you are wanting a blank form to fill out, use the acFormAdd instead of the read only.

Have fun! -Dustin
Rom 8:28