Moving on...

[jeo]

I think I got my relationship lesson down and now that I'm trying to move on to create some forms I'm running into problems again.
I'm attaching a sample of my database, so hopefully someone could put me back on the right track.
I have 2 forms frmDates and frmProjectStaff.
On my ProjectStaff from, I can add a new record or view a record for a particular project, for a particular resource.
When I click to assign Dates for this Resource, for this Project button, the ProjectName on the next form doesn't change...see attached screen prints.
Is it possible to make so the project name does change(to the appropriate record), when I change it on a previous form?
And my second question is - dates...right now if I change dates for 1 person on a certain project, it changes those dates on all projects for that person.
I would like to keep those dates so I know who is where and when.
Thank you in advance for all your help!

 

[monkeytunes]

Hey Jeo,

I took a quick glance at your tables, and if I'm looking at it correctly, your table structure is wrong. You should be storing dates in the joined table, i.e. tblProjectStaff. tblProject will probably rarely change (only as new projects come your way), and tblStaff will rarely change (only as employees come and go), but as your Staff works on different Projects, tblProjectStaff will grow and grow. You need two more columns (that I can see) in tblProjectStaff - "ProjectDate" which will be for dates (DON'T use "Date", as Access reserves that word for special functions), and "ProjectEvent" for determining if it's a Start Date, End Date, what-have-you. (And this can be a lookup to yet another table, something like "tblEvents" where you can list different things that can happen to projects.)

When you build your form, trying basing it on "tblProjectsStaff", and use dropdown lists based on the other tables to populate it. Hope that helps!

 

[jeo]

that was great advice, now one more thing how can i create 2 coditions on which a form opens? Right now there is:
stLinkCriteria = "[Project_ID]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I want something like stLinkCriteria2 = "[StaffID]=" & Me![Combo2]
DoCmd.OpenForm stDocName, , , stLinkCriteria2
condition on there as well, so the form opens when both are met.

Is there a way to do that? Can i use like an "And" or may be i need to add something into this string...DoCmd.OpenForm stDocName, , , stLinkCriteria...not sure.
Thanks!

 

[WayneRyan]

jeo,

stLinkCriteria = "[Project_ID]=" & Me!Combo0 & " And " & _
                 "[StaffID]=" & Me.Combo2
Or:
stLinkCriteria = "([Project_ID]=" & Me.Combo0 & " Or " & _
                   IsNull(Me.Combo) & ") And " & _
                 "([StaffID]=" & Me![Combo2] Or " & _
                   IsNull(Me.Combo) & ")"

Wayne

 

[jeo]

Thank you, thank you!
That works beatifully!