Using combo box to "branch" to two different forms 2

[Conner]

I have a "master" form that the user inputs common data (address, phone, etc) for everyone in a school system (FrmEveryone)--"1" side. Everyone placed in the database is classified in a combo box as either an "employee" or a "student."

Once this classification is made, I have two command buttons that require the used to select one of two additional forms -- one for data unique to an employee(FrmEmployee)--"many side" and the other for data needed on students (FrmStudent)--"many side".

My question is this: How do I "automate" going from the master form to the appropriate form when the user selects either "employee" or "student"? Can this be done without the user "interacting" with command buttons?

I would like to see this done with Macros (I am studing them now) but realize that VBA (which I do not yet know) is a better approach. Obviously, I am a novice so please move very slowly and make no assumptions.

I can follow directions. Thanks in advance!

 

[DougP]

use VBA code

In the Afterupdate event of the combo box try this

Private Sub Combo0_AfterUpdate()
If Combo0.Text = "Employee" Then
DoCmd.OpenForm "frm-Employee"
Else
DoCmd.OpenForm "frm-Students"
End If
End Sub

I just tested this DEMO and it works great
AS soon as I pick the appropriate choice in the combo box it opens the correct form

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[Conner]

In the "if" sentence where you say "If Combo0.Text="Employee" then..." what exactly is the "0?" The name of my combo box is "Classification." I've tried everything I can think of, but I always get a "Run-time error 424: Object required." I'm researching this now, but VBA is new, new, new...

 

[Telsa]

I would use Me.Classification instead of combo0.text.

HTH

Mary )

 

[Conner]

IT WORKED! IT WORKED! And it worked exactly like it was suppose to....which is a rare event in my life. Thanks to you and to DougP.

 

[Conner]

oops. Spoke to soon. It gets me from the master form to the appropriate form, but records not in Sync. I know how to "sync" using a macro and a command button, but can I put all this in as part of this subprocedure.
I was linking the two forms with a command button that had the Macro argument [SSN]=[Forms]![frmEmployee]![SSN]but how do I do this now that I am "automating" without using command buttons -- Is this going to be similar to what you just showed me -- a second IF-Then-Else embedded in this subprocedure? It never ends, does it?

Summary: How do I "sync" the records between First form and each of the appropriate two other forms?

 

[DougP]

Just add a few more parameters.
Question if you are on a main form do you havve both a unique ID for students and Employee
Like for students you have StudentID
Employees have EmpID

add this to the end of the lines
acNormal, , "EmpID = " & Me!UniqueID

here is an example:
Private Sub Combo0_AfterUpdate()
If Combo0.Text = "Employee" Then
DoCmd.OpenForm "frm-Employee", acNormal, , "EmpID = " & Me!UniqueID
Else
DoCmd.OpenForm "frm-Students", acNormal, , "StudentID = " & Me!UniqueID
End If
End Sub


DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.
Or visit my WEB site

http://www.barcodeone.com

 

[Conner]

The Social Security Number (SSN) is used as the "Natural" unique ID for both Employee and Student. The SSN is entered into the FIRST of MAINForm, and then when the individual is classifed as either a "student" or "employee", the second appropriate form appears. There is a main table named "tblEveryone" that links the SSN (Primary Key) as a "shared" primary key with two more tables -- tblStudent and tblEmployee. The two SECONDARY tables each have a unique primary key, i.e. StudentID and EmployeeID.

Hopefully I can keep the SSN because this is part of my budding validation plan. You can't be enrolled in the school system unless you have a SSN as required by our State law.

How does this change things?

 

[DougP]

just change end of both DoCmd lines to reflect the social security number like so.

DoCmd.OpenForm "frm-Employee", acNormal, , "SSN = " & Me!SSN

The "SSN" must be the exact spelling of your Social security field and in the Me!SSN
"Me" refers to the current form and "SSN" again is the name of the TEXTBOX on the current form

So what that DoCmd does, is open a particular form and finds a record whose social security number is eqaul to the number keyed in the SSN text box on the current form.



DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.
Or visit my WEB site

http://www.barcodeone.com

 

[Conner]

I tried what you suggested:
I now how the following Event Procedure in AfterUpdate:

Private Sub Classification_AfterUpdate()
If Me.Classification="Employee" Then
DoCmd.OpenForm "frmEmployee",acNormal,,"SSN="&Me!SSN
Else
DoCmd.OpenForm "frmStudent",acNormal,,"SSN="&Me!SSN
End IF

When I select "student" from the combo box, I now get "Run-time error 13:type mismatch."

When I select "employee" from the combo box, I get "Run-time error 2501: The OpenForm action was cancelled."

I suspected that I had some macros and commands from other experiements that might be causing a problem, so I cleared every command, macro, etc except this one Event procedure above, saved everything, and reopened.

I still get the same error messages. When I create a command button usine the SSN primary key and SSN join field in either of the two 'secondary' forms, the FrmEveryone is in Sync with either of the forms. Keep in mind, I want the above event procedure to autmate this...Where do I go now to investigate the problem?

 

[Telsa]

You know what I would have done??? I would have set up in a Module a variable to capture the SSN from the first form. Then use that variable to copy the SSN at the moment of clicking the button and then copying the info to the appropriate field when the new form opened.

Is that too lame I wonder?

Mary

 

[adame]

Does anybody know why an Access field (SystemID) in my database seems to have disappeared?

It is fully visible, but the code can't find it.

The code is in a openform command, when applying the filter. The code for this came from the access wizard, which could see the field, but now can't. Can anybody shed any light on why it can't be seen?

Strange thing is, that other code in the database can find it.

Thanks,


Adam

 

[Conner]

Lame? At this point, I would just be happy to be conscious.Crawling comes next. Then we'll talk about being lame.

This data base serves two purposes -- it is an "experiment" by management to "show" that it is time for this school system to manage its own data.(Wo unto those who lead faster than those who seek to follow) As a very senior manager(old, tired, limited trainability), I volunteered for the project simply because I want to learn more about Access, VBA, etc.

So....what you have is a database that is a "learning tool.'

In time, we will achieve our goal of an "automated" database in which employees and students under our supervision can input and output what we need to make good decisions. And we'll know enough to talk with knowledge to the other side - programmers like you and Doug...

My point in this: We are trying to learn and nothing is too "lame" or beyond what we are seeking.

Right now, my central problem is that what DougP has kindly given me, these forms should be automated and in-sync. Every resource and help-guide lead me to the same point he is directing me....So why isn't the above procedure working?

Keep in mind half of it does -- the "automation" between the forms works. It's only when I add the ",acNormal,,"SSN=&Me!SSN" that I get those error messages. Also Keep in mind that command buttons and macros build about the "SSN" fields in all the forms do sync the forms. Should I conclude from that that "SSN" is apprpriate and in fine working order in all tables and fields? What am I missing here?

By the way, can you give a few more study hints on your suggestion: "I would have set up in a module a variable to capture the SSN from the first form. Then use that variable to copy the SSN at the moment of clicking the button and then copying the info to the appropriate field when the new form opened." I'd like to know more, but keep in mind I want to know what seems like a perfectly good procedure as built by Doug isn't working...

 

[Telsa]

I built a database where certain fields needed to be copied to the next form.

Anyway, what I did is in the Module, I created a variable to capture my field's data. Since the variable is in a Module and not a form, it can be shared between forms.

And when the user requests that the data be copied, it would capture the appropriate fields, open the new form, and populate the appropriate fields using the data captured in the variables.

Here is what I put in a module:

Public strProdNum As String
Public strPartsBase As String
Public strProject As String

And in the form, I would simply (when ready to go to next form) capture the data with

strProdNum = Me.ProdNum
strPartsBase = Me.Base
strProject = Me.Project

then have the it launch the next form.

And in the second form, upon opening a new record

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
Me.ProdNum = strProdNum
Me.Base = strPartsBase
Me.Project = strProject

End Sub

I would have it populate the fields by doing the reverse!

Now, I'm probably doing things the hard way... but it worked for me. I'm just a wannabe database administrator/programmer trying to do things on my own. But I have found some codes seem to have a way of beating around the bush to do the simplest things. And I like simplicity... even in coding.

Also, I have found I have less problems when I capture field values in a variable and then use that in my automation.

Did that make sense??

Mary

 

[cdjconsulting]

Why kill yourself with code and VB. Why not have them push a button, labeled with one or the other title, that enters the appropriate title, that takes them to the appropriate form?