Repost - Open Form

[Guest]

Hi,

Please forgive the reposting, but my original post didn't receive an answer
and I'm really hoping someone can help.

I have an unbound form 'fTTSwitchboard' that has two combo boxes - one lists
tech names 'CboTech' and the other lists job numbers 'CboJobNumberSelect.
There is also a subform for list of jobs assigned to CboTech. The
JobNumberSelect combo box lets them enter a job number that is not on their
list and a form 'fGeneralInfoTT' opens giving specific about job with a
subform 'fWorkLog' that has a combo box 'CboTech' where they can select their
name and text boxes to enter start and stop times.

Everything works well now, but I would like to tweek it. When the form
'fGeneralInfoTT' opens, I would like the subform 'fWorkLog' to have the
'CboTech' name filled in with the name entered from 'fTTSwitchboard'. I can
make it do this but the problem is if I enter Joe on the first form, I can
open a job and have Joe's name filled in the combo box on the subform of the
second form. I enter start time for Joe, close form and go back to the
switchboard and click on Mike. I select same job for Mike and it will open
with his name, but with Joe's time. I'm not sure what I need to do to get
it to open with a fresh screen for Mike, but at the same time when Joe
selects his name from the switchboard, have it open to his record with the
previous start and empty stop time. I've tried openargs, but couldn't get it
to work. Currently this is what I have.


Private Sub CboJobNumberSelect_AfterUpdate()
On Error GoTo CboJobNumberSelect_AfterUpdate_Error
If IsNull(Forms!fttworkloghiddenopen!StopTime) Then
DoCmd.OpenForm "fTTWorkLogReminder"
End If

DoCmd.OpenForm "fGeneralInfoTT", , , "JobNumber=" &
Me!CboJobNumberSelect
CboJobNumberSelect_AfterUpdate_Exit:
Exit Sub

CboJobNumberSelect_AfterUpdate_Error:

If Err.Number = 2501 Then
DoCmd.OpenForm "fGeneralInfoTT", , , "JobNumber=" &
Me!CboJobNumberSelect
Else
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
GoTo CboJobNumberSelect_AfterUpdate_Exit

End Sub

If anyone has any ideas or suggestions, I would really appreciate hearing
from you.
Thanks,
Pam

 

[Jeff Boyce]

If, when you (re-)open a form, the previously-entered value is still showing
in the control, then there's a good chance that the control is "unbound"
(i.e., not connected to an underlying field from a table).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,
Thanks for answering. The Tech combo box in the subform was unbound. I
made the control source Tech from the table backing form. It puts the name
in the table, but it still opens with second name selected, but first name's
start time.
Any ideas?
Thanks,
Pam

 

[Jeff Boyce]

I'm having trouble visualizing what you are describing...

What "name"? What "start time"? Are those bound controls?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

I'm sorry for the confusion, please let me try to better explain this.
Db opens with "fTTSwitchboard"
Unbound combo box "CboTech" lists tech names
Unbound combo box "CboJobSelect" lists job numbers in db
Tech selects his name from CboTech and a job from CboJobSelect
Form opens "fTTGeneralInfo" that lists info about job
Subform "fTTWorkLog" on main form "fTTGeneralInfo" has fields "Tech",
"StartTime" and "StopTime"
Currently, when tech selects his name from combo on switchboard, general
info opens with his name selected in the "tech" field on worklog subform. He
enters his starttime, closes form and begins work. If another tech is also
working on this same job number, he will do the same, BUT when he opens the
general info form, his name appears, BUT it still has the same starttime as
the first tech entered. I'm not sure how to go about getting a record to
open with the second tech's name already entered with blank field for start
time.

Here's the code I have for CboJobNumberSelect:

Private Sub CboJobNumberSelect_AfterUpdate()
On Error GoTo CboJobNumberSelect_AfterUpdate_Error
If IsNull(Forms!fttworkloghiddenopen!StopTime) Then
DoCmd.OpenForm "fTTWorkLogReminder"
End If

DoCmd.OpenForm "fGeneralInfoTT", , , "JobNumber=" &
Me!CboJobNumberSelect

CboJobNumberSelect_AfterUpdate_Exit:
Exit Sub

CboJobNumberSelect_AfterUpdate_Error:

If Err.Number = 2501 Then
DoCmd.OpenForm "fGeneralInfoTT", , , "JobNumber=" &
Me!CboJobNumberSelect
Else
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
GoTo CboJobNumberSelect_AfterUpdate_Exit

End Sub

I hope this answers your questions and you'll be able to help me with this.
Thanks,
Pam

 

[Jeff Boyce]

Pam

I guess I'm not understanding why code is involved. It sounds like you want
to have a main form (the data in which comes from a selection of Tech and
Job), and a subform (the data in which is the work records belonging to that
Tech & Job).

You can do that using main form/subform construction in Access. Access will
even keep the main form and subform records "in sync" for you if you tell it
which fields to use.

Note -- it all starts with the data... for the above to work (and for you to
make the best use of Access' relationally-oriented features/functions) your
data needs to be well-normalized. Just based on this portion of your
description, you'd need a table that holds TechNames, a table that holds
Jobs, a table that connects Techs to Jobs, and a table that hold WorkLog
records for Tech/Job combinations. And you'd need to have foreign keys in
those latter two tables so the records can properly point back to their
"owners".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Maybe I'm not explaining myself well enough for you to understand what I'm
trying to do. At this point, I'm not even sure if it can be done or if it's
worth the effort.

I have a table for Tech Names, a table for Jobs, and a WorkLog table brings
them together. I wanted the subform that is on the main form to open with
the name of the tech in the combo in the switchboard. The main form that the
subform is on is linked to the subform by job number.

I already have everything in place and is working well. I just wanted to
simplify things by having the subform open with name already entered.
Thanks,
Pam

 

[Jeff Boyce]

Pam

Why do you want to duplicate the TechName in the work record? A
mainform/subform construction would let Access put the Tech's ID in the work
record (think relational database).

Let's go back to the data. I still don't have a clear picture of what data
you have and how you've organized it.

Forget about how you now have the data structured for a moment. Think about
the real-world situation you're dealing with. Take a look at the statements
I'm guessing about below and let us know if they are accurate...

One Tech can work on Many Jobs.

One Job can be worked on by Many Techs.

One Job can have Many WorkLog records.

One WorkLog record pertains to One and Only One Job.

If those statements fairly represent the world you are working in, then one
way to organize/normalize your data would be:

tblTechPerson
TechPersonID
FName
LName
DOB
... other person-specific info

tblJob
JobID
JobTitle
Description
DateOriginated
OriginatedBy
... other Job-specific info

trelPersonJobAssignment
PersonJobAssignmentID
TechPersonID
JobID
AssignmentDate
... other information specific to the assignment of one tech to one job

trelWorkLog
WorkLogID
PersonJobAssignmentID
BeginDateTime
EndDateTime
ActivityDescription
... other information specific to what this person did on this job
(found by tracking back through trelPersonJobAssignment) during this time
interval

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pam]

Jeff,

I appreciate your input. The tables and statements you listed are pretty
much how my database is set up. I don't understand your first question when
you ask about duplicating TechName in the work record. It's only entered
one time in the subform "fTTWorkLog" and is entered to the table "tWorkLog".
If you're thinking because they select their name in the switchboard, this
is an unbound combo box that gives a list of their jobs assigned to them to
select what they want to work on.

Again, I thank you for your time and effort, but I think I will leave well
enough alone. Undoubtedly, we aren't understanding each other. It was just
a little tweaking I wanted to do to try to eliminate human errors.

Pam

 

[Jeff Boyce]

Pam

There are a lot of folks who frequent these groups. Consider re-posting
(one more time) to get your question noticed by others. Just because I
couldn't understand doesn't mean someone else won't be able to help.

Regards

Jeff Boyce
Microsoft Office/Access MVP