I know I (and others) have asked various forms of this question before, but I am still struggling with this. Please be patient, I want to explain myself well so that I get useful input (of course any input is better than none at all!)
I have a form of "General Contacts" that will be the master form for everyone else in my office to use to view/add/edit, etc. data for all those we contact and vice versa. Everybody, however, has their own little databases that they attempt to maintain, which, when I'm done, will basically be subsets of the main contacts db. The contacts db has a series of "qualifier" fields that will denote how each contact is associated with our program. Based on that information, each other person in my office who has smaller dbs will be able to select (by query, filter, etc.) their subset of data to work with. Right now each of these fields is a Yes/No value list (though I may change that to a basic yes/no field).
When a person is entered into this contacts table, and I select "participant=yes" as a qualifier for that person, I would like to open the "participants" form and update it (because it is based on a query between the contacts and the participants tables b/c the participant's table has extra info for tracking just those individuals) by pasting the id into the participants table. That part I've gotten down OK. What I'm having trouble with is if the person is already in the contacts table (say with qualifier as "Newsletter=Yes") and now they are also a participant. I have a macro attached to the "participant" qualifier on after update:
RunCommand: Save Record (in contacts form)
OpenForm: Participants
ApplyFilter: [Forms]![CONTACTS]![ContactID]=[Forms]![PARTICIPANTS]![PART ID]
On the condition: [Forms]![CONTACTS]![ContactID]=[Forms]![PARTICIPANTS]![PART ID]
GoToRecord: New
On the condition: [Forms]![PARTICIPANTS]![PART ID]<>[Forms]![CONTACTS]![ContactID]
SetValue: [Part ID]=[ContactID]
This is an effort to look for the existing id, and if it exists, go to that record, and if not, then create a new record, setting the id value to match. Oddly enough, it works for the very first person in my Contacts table (i.e. since he's in there already, it opens the participants form at his record), but when I try it with any others, it opens the participants form and creates a new record (which is no good, then I also get the "duplicate values in primary key" error/problem).
Another approach I've tried, at the suggestion from this forum, is to create an unbound textbox on the participants form with the following code (again, an attempt to "count" number of existing records with matching ids).
Private Sub DUMMY_AfterUpdate()
Dim x As Integer
x = DCount("*", "[Participants Table Query]", "[PART ID]=" & Me.DUMMY)
If (x > 0) Then
DoCmd.ApplyFilter ("[PART ID]=" & Me.DUMMY)
DoCmd.GoToRecord ([acNewRec])
DoCmd.GoToControl ([PART ID])
End If
End Sub
But upon attempting this (i.e. typing an existing id into the dummy field, I get an error from the "docmd.gotorecord" part of sytax error. If anyone sees ANYTHING wrong with this code, please let me know...I'm not too good with VB yet.
I think this is good for now. Sorry this is so long, but I needed to explain myself (as I hope I've done). Feel free to email me for more in-depth discussion/troubleshooting. I'm sure I'll have more problems for you all to help with. Thanks so far!
[This message has been edited by lscheer (edited 09-11-2001).]
I have a form of "General Contacts" that will be the master form for everyone else in my office to use to view/add/edit, etc. data for all those we contact and vice versa. Everybody, however, has their own little databases that they attempt to maintain, which, when I'm done, will basically be subsets of the main contacts db. The contacts db has a series of "qualifier" fields that will denote how each contact is associated with our program. Based on that information, each other person in my office who has smaller dbs will be able to select (by query, filter, etc.) their subset of data to work with. Right now each of these fields is a Yes/No value list (though I may change that to a basic yes/no field).
When a person is entered into this contacts table, and I select "participant=yes" as a qualifier for that person, I would like to open the "participants" form and update it (because it is based on a query between the contacts and the participants tables b/c the participant's table has extra info for tracking just those individuals) by pasting the id into the participants table. That part I've gotten down OK. What I'm having trouble with is if the person is already in the contacts table (say with qualifier as "Newsletter=Yes") and now they are also a participant. I have a macro attached to the "participant" qualifier on after update:
RunCommand: Save Record (in contacts form)
OpenForm: Participants
ApplyFilter: [Forms]![CONTACTS]![ContactID]=[Forms]![PARTICIPANTS]![PART ID]
On the condition: [Forms]![CONTACTS]![ContactID]=[Forms]![PARTICIPANTS]![PART ID]
GoToRecord: New
On the condition: [Forms]![PARTICIPANTS]![PART ID]<>[Forms]![CONTACTS]![ContactID]
SetValue: [Part ID]=[ContactID]
This is an effort to look for the existing id, and if it exists, go to that record, and if not, then create a new record, setting the id value to match. Oddly enough, it works for the very first person in my Contacts table (i.e. since he's in there already, it opens the participants form at his record), but when I try it with any others, it opens the participants form and creates a new record (which is no good, then I also get the "duplicate values in primary key" error/problem).
Another approach I've tried, at the suggestion from this forum, is to create an unbound textbox on the participants form with the following code (again, an attempt to "count" number of existing records with matching ids).
Private Sub DUMMY_AfterUpdate()
Dim x As Integer
x = DCount("*", "[Participants Table Query]", "[PART ID]=" & Me.DUMMY)
If (x > 0) Then
DoCmd.ApplyFilter ("[PART ID]=" & Me.DUMMY)
DoCmd.GoToRecord ([acNewRec])
DoCmd.GoToControl ([PART ID])
End If
End Sub
But upon attempting this (i.e. typing an existing id into the dummy field, I get an error from the "docmd.gotorecord" part of sytax error. If anyone sees ANYTHING wrong with this code, please let me know...I'm not too good with VB yet.
I think this is good for now. Sorry this is so long, but I needed to explain myself (as I hope I've done). Feel free to email me for more in-depth discussion/troubleshooting. I'm sure I'll have more problems for you all to help with. Thanks so far!
[This message has been edited by lscheer (edited 09-11-2001).]
