Expression typed incorrectly/too complex? 1

[Lunatic]

First, I'm dangerous, I have learned what little VBA I know in a very ad-hoc manner so I may have messed up some very fundemental steps.

Problem: When I try to load a form with a sub-form, I receive the following error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

It seems to be related to the subform, because the main form will load after clicking 'OK', but the subform is just a big white square.



What I'm trying to do: I have 3 tables. A staff table, a project table, and a share table.

The staff table populates the main form, and has StaffID as a primary key. The share table populates the sub-form and contains foreign keys from both the Staff and Project tables. The Project table contains a list of information that I want to use to update records in the Share table.

So functionally, the user accesses the staff member of choice. Then in the subform, selects the project the staff member is associated with, and other information auto-populates the rest of the sub-form based on the project selection.

Table names = tbl_Staff_Master, tbl_Share, tbl_Projects

I only have 1 event procedure in the subform, and it was working as expected before I added the chunk in red:

Private Sub Combo53_Change()

    Dim stProjIDTemp As String
    Dim stStaffIDTemp As String
    
    stProjIDTemp = "ProjID"
    stStaffIDTemp = "staffID"

Me![Date Set 1].Value = Date
Me.Dirty = False
[red]
    WHERE staffID = StaffIDTemp, ProjID = ProjIDTemp
    Set tbl_Share.[Project Contact 1] = [tbl_Projects]![Project Contact] And tbl_Share.[Date Start 1] = [tbl_Projects]![Date Start] And tbl_Share.[Date End 1] = [tbl_Projects]![Date End]
[/red]

Me![Comment 1].SetFocus
DoCmd.Save
End Sub

Any helps, hints, suggestions, constructive critisim, or other productive comments are very much appriciated!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[Remou]

Is that a cut and paste from your module? You have put a chuck of SQL where VBA is needed. What did you wish to do?

 

[Lunatic]

Yeah, thats probably the sum of it.

What I'm attempting to do is when project is selected from a combo box 3 fields, "Contact Name", "Start Date", and "End Date" are filled in based on the data associated with the project.

So in tbl_Projects, Project X maybe have a contact of BOB, a start date of 5/08 and an end date of 10/08.

What I want is when Project X is chosen in the subform, the record for that staff member in tbl_Share is updated so the record contains the contact information above.

Am I making or sense, or just confusing things more?

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[Remou]

I think that your set up may not be quite right.


You have a contact table:

ContactID
ContactDetails

A project table:

ProjectID
ProjectDetails

You have a share table:

ContactID
ProjectID

The subform should show a list based on a join between the share table and the contact table with a Link Child / Link master of ProjectID.

Are we looking at the same idea?

 

[Lunatic]

So my problem is I'm trying to insert data into the share table, rather than display it from the project table?

The Staff and Project table descriptions are accurate (if you replace contact with staff). But right now the share table is more like this:

StaffID
ProjectID
project details based on StaffID + ProjectID
other share details (date of contact, which is auto generated and the comments field).

The Staff and Project tables are pretty static. The Share table starts out empty until another project contacts us about having our staff work on their project as well, then a record of the discussion to share staff is recorded.

That is the purpose of the entire database, is to track the discussions that take place and the outcomes.

Am I just muddying everything up?

Thank you for putting up with what must be pretty elementry questions.

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[Remou]

Ok. I think I see what you want. You have a number of choices, and one is certainly an append query. However, you may wish to consider using several link child and master fields. Remember that a link master field can refer to a control.

An append query would look something like:

strSQL="INSERT INTO tblShare (ProjectID, ContactID, SomeDate) Values (" _
& Me.txtProjectID & "," & Me.cboContact & ",#" & Now() & "#)"
CurrentDB.Execute strSQL, dbFailOnError

Note that the delimiter for dates is hash signs (#) and for text is singles quote (').

 

[Lunatic]

I'm not sure an append query would work without rebuilding the table structure as you suggest, and I'm not sure I understand table design well enough to build it. I've bitten off more than I can chew it looks like.

I'm going to my backup plan, which is a brute-force, update all records each time rather than the single record at issue.

Remou, thank you for all your help, I'm just not up to the level to do what I was thinking of doing and I'd waste your time trying to parse it out. Thank you again for the assistance!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum