How to link field

**jmk909er** · 10-23-2010, 04:18 PM

Probably gonna need also to make a new command button on one of the forms so the are not both executing the same code???

**jmk909er** · 10-23-2010, 04:27 PM

Paul, you are a genious! I pasted it in and it is working great. Should I leave out the Me.Dirty thing?

**jmk909er** · 10-23-2010, 04:51 PM

Paul I have one more bug (that I know of) to work out still, maybe you want to play with it since you have a copy.

The users will only have access through the switch board. When you are in frmSwitchboard and click on the "Add New Project" button I need it to be a requirement that a comment also be made. The reason being that when my query runs to generate the report it will not pick up any projects that do not have a comment since projects and comments are linked.

Either that or the query somehow needs to pick up projects with no comments (I have not been able to do that)

I was thinking that when a new project is created, that if they try to close the form a pop up message would tell the user that a comment is required or it will not save. I am a little over my head with this one.

Thanks, Joe

**slave138** · 10-23-2010, 05:16 PM

In the BeforeUpdate event of the Form use something like this:

Code:

    Dim Msg As Variant
    'If there are changes to the record and the Comment field is null...
    If Me.Dirty And IsNull(Me.txtComment.Value) Then
        'Ask the user if they want to go back to add a comment...
        Msg = MsgBox("The record will not be saved without a comment." & vbCrLf & "Would you like to go back and add a comment?", vbYesNo)
        'If they want to go back, cancel the event (closing the form, moving to a new record, etc...) and set the focus to the Comment field
        If Msg = vbYes Then
            DoCmd.CancelEvent
            Me.txtComment.SetFocus
        'Otherwise undo the changes and allow the event to process
        Else
            DoCmd.RunCommand acCmdUndo
        End If
    End If

**jmk909er** · 10-23-2010, 05:31 PM

Hey slave138 I am pretty much a novice so I need some clarification, do I use this code as is or do I need to add a form name in this where it says "Me.txtComment.Value" or where it says "Me.txtComment.SetFocus" ??

**slave138** · 10-23-2010, 05:38 PM

Change txtComment to be whatever name you have for your comment field and make sure you replace all instances of it.

**jmk909er** · 10-23-2010, 05:49 PM

I just noticed that in the BeforeUpdate event of this form I already have:

'Tracks when a change on the form was made and by who
Private Sub Form_BeforeUpdate(Cancel As Integer)
[Changed By] = Environ("username")
[Data Changed] = Now()
End Sub

How do I incorporate all this together or can I and also the name of the comment field is "Comment"

**pbaldy** · 10-23-2010, 05:52 PM

You shouldn't need a comment. Edit the join between the two tables and change it to include all records from the projects table.

**slave138** · 10-23-2010, 06:01 PM

Originally Posted by jmk909er

I just noticed that in the BeforeUpdate event of this form I already have:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Msg As Variant
'If there are changes to the record and the Comment field is null...
    If Me.Dirty And IsNull(Me.txtComment.Value) Then
'Ask the user if they want to go back to add a comment... 
        Msg = MsgBox("The record will not be saved without a comment." & vbCrLf & "Would you like to go back and add a comment?", vbYesNo)
'If they want to go back, cancel the event (closing the form, moving to a new record, etc...) and set the focus to the Comment field
        If Msg = vbYes Then
            DoCmd.CancelEvent
            Me.txtComment.SetFocus
'Otherwise undo the changes and allow the event to process
        Else
            DoCmd.RunCommand acCmdUndo
        End If
    Else
'Tracks when a change on the form was made and by who
[Changed By] = Environ("username")
[Data Changed] = Now()
    End If
End Sub

How do I incorporate all this together or can I and also the name of the comment field is "Comment"

This is just one of the ways you can do it.

**jmk909er** · 10-23-2010, 06:09 PM

Hey Paul that sounds good but I can't get it to work I am clicking on "database tools", "Relationships", "Edit Relationships" Please take a look at the attached screen shot and see if I am in the right place or not and what I need to do

**jmk909er** · 10-23-2010, 06:11 PM

doc not uploading I will try again

**jmk909er** · 10-23-2010, 06:14 PM

Would not save word doc so I zipped it

**jmk909er** · 10-23-2010, 06:38 PM

Paul, I think I did it right but my query still wont pick it up because my query consists of a table combined with a query that only returns the most recent comment instead of all comments so that my report will only have the latest comment and I think that is why it will not return a record that does not have a comment. Am I right?

**pbaldy** · 10-23-2010, 06:44 PM

Like this, but change INNER to LEFT:

http://www.baldyweb.com/LastValue.htm

Relationships is not where I mean; you'd edit the join in query design view by right clicking on the join line. That has the same effect as changing INNER to LEFT as I described above.

**jmk909er** · 10-23-2010, 07:14 PM

YES! Thank you Paul and also slave138 I never know about that trick of right clicking on the connector in query mode. Everything is working GREAT I will have to run it through all it paces to be sure but I thing all the bugs are out. I will be the hero at work thanks to you. Awesome, thanks, Joe

How to link field

Thread Tools

Change relationship

Doc

Similar Threads

Link Master Field and Link Child Field

Link Master Field Error

how can i link a field of atable to other one that has always the same data?

Link ComboBox to field in a table

Create the link

Posting Permissions