Link two queries without common fields

[orange]

A few things to consider-
-if you use a naming convention using only alphanumerics and "_", you will save yourself syntax errors down the road
-I would avoid spaces in field and object names


-Employee
-there is probably more info about Employee that should go in this table

-CI Table
- this table may need a more descriptive name to be meaningful to others
-there may be a hidden Entity Project in here?? I think there is.
-Stage1, Stage2... jump out as a hidden Entity
-can a project only have 1 owner??
-I think you have a hidden Entity Status
-If a Project can have many Statuses, and a Status can refer to man y Projects, then you have M:M
-you will need to resolve this with a junction table eg ProjectStatus
-this junction table will probably need a multifield unique index that includes a Date eg StatusDate
-Does a DueDate ever get modified? Do you need to record it?
-NC_IPR ???? What is it?
-again no special characters %? or space in field names

NCRTable
-CauseId must point to another table eg Causes (better to have clear definitions of these)
-contains DepartmentId with no link to Department?????
-contains TypeId with no link to a Type table????
-contains PressID with no link to Press????
-NC_IPR why is this field in this table and the CI table
-Vendor may indicate another Entity (Vendor Table with vendor info)
-owner of what???
-status indicates 1 status and should be confirmed??
-Qty Affected what is the context exactly for this
-Qty Rejected what is the context exactly for this
-operator could be a hidden Entity Operator Table with operator info????
-ReportedBy -is this an Employee??
-SPM ??? you should use meaningful names --someone else may have to maintain/alter this application
-GrossWt of what (another possible hidden entity)
-C/A ???


-CreateProject???


Those are a few points to review and consider. As per previous posts, you really need to get your tables and relationships created, tested and confirmed against business rules before you get into the Forms etc.
Your data model needs work, but you may want spend a little time documenting the business facts and ensuring you have all the info needed to build the database.

Good luck with your project

[JGrots]

TEST - Continual Improvement Projects.zip I created a sample DB that is the bare essentials I need in order to try to get a version of what I want. I have two forms, CI Project Form and NCR Form. In the NCR Form there is an option button to create a project. If the button is clicked Yes, then a new project (record) will be created in the CI Project Form where the NCR_IPR number and the C/A or P/A field from the NCR Form will automatically populate into the NCR_IPR field and the Description field, respectively, in the CI Project Form. I also want to be able to create new projects in the CI Project Form without going through the NCR Form. What coding do I need in order to do this? I'm very limited in my VBA or SQL knowledge. Any help would be greatly appreciated. Thank you!

[June7]

Don't need any code. Just open the CI Project Form, move to new record and enter data.

If you want code to automate generation of next Project Number, that is another issue. Not seeing any code that does that if the the CI Project Form is opened from the NCR Form.

I see an OptionGroup control with two check boxes, not a Button. Not seeing the code you describe.

[JGrots]

June7, thanks for replying. The problem isn't creating a new record in the CI Project Form from the CI Project Form, it is creating a new record in the CI Project from the NCR Form. I want to check the "Yes" box of the OptionGroup Control (not button, my mistake) in the NCR Form and have a new record created in the CI Project Form with the information in the NCR_IPR # and the CA_PA fields from the NCR Form being filled into the NCR_IPR and Description fields of the CI Project Form, respectively. Can this be done? Am I making sense in what I want?

[June7]

Yes, it can be done requires VBA code. Code behind the CI Project Form can refer to controls of NCR Form and populate the fields.

The complication is you only want this to happen when the CI form is opened by the NCR form. One way to handle is by use of OpenArgs argument of DoCmd.OpenForm.

DoCmd.OpenForm "CI Project Form", , , , , , "NCR"


Code behind CI form

Sub Form_Current()
If Me.OpenArgs = "NCR" Then
Me!Description = Forms![NCR Form]!Description
Me!NCR_IPR = Forms![NCR Form]![NCR_IPR #]
End If
End Sub

Or the OpenArgs can pass the values and the CI Form can extract from OpenArgs with string manipulation functions. Always more than one way ...

BTW, advise no spaces or special characters/punctuation (underscore is exception) in names. Better would be NCR_IPR_Num.

[JGrots]

Where does the " DoCmd.OpenForm "CI Project Form", , , , , , "NCR" " go? Does it go in the CI Form or the NCR Form and under which event?

[June7]

It goes behind NCR form, probably in a button Click event.

[JGrots]

Okay, so I added the code to the appropriate places and it works in opening the CI Form and adding the NCR_IPR and CA_PA from the NCR Form to their respective fields in the CI Form. However, endless records are being created with the same NCR_IPR and CA_PA text and all the records that were previously created are being overwritten. As I keep scrolling in the CI Form, more and more records are created. How do I only create one new record? I attached the updated DB TEST - Continual Improvement Projects (2).zip

[June7]

It's because of the OpenArgs. The Current event runs whenever moving to new record. Could have different condition to IF.

If Me.Project__ = "CI13--" Then

Or use both conditions. Or move the Current event code to the Load event after the GoToRecord.

[JGrots]

I have 2 more problems:

1) When I click the "yes" box in the NCR Form, the CI Form opens but only the NCR_IPR field populates. The Description field does not come in. However, If I close both forms, then reopen the NCR Form and click the "No" Box then click the "Yes" box, the CI Form will open up and a new record will be created with both the NCR_IPR and Description fields populated. Do you know why this might happen?

2)I want the the CI Form to close right after it is opened by the NCR Form and the fields are populated. I tried the following and it closed the form like I wanted but only the NCR_IPR field was populating not the Description field.

Private Sub Frame374_Click()
If Me!CreateProj = 1 Then
DoCmd.OpenForm "CI Project Form", , , , , , "NCR"
DoCmd.Close
End If

End Sub

[June7]

Can you provide latest version of db?

[JGrots]

Here it is: TEST - Continual Improvement Projects (3).zip

[June7]

Issue appears to be Memo field type. I changed both fields to Text type and then works.