I have a form that user inputs a protocol number into. This form is called "Protocol_Index". When the user first inputs this protocol number, right after they have to to another form, which they easily do by pressing a button that takes them there. However when I have all the data in the tables for both "Couple_Index" and "Protocol_Index" this seems to work fine. What happens is when they are searching for a record based on a protocol number in the "Protocol_Index" form they simply press a button to take them to the "Couple_Index" form. Normally the couples information is there to see based on the protocol number from the previous form (Protocol_Index). But this only happens when all data is manaully changed. If I'm creating a new record, this doesnt work. Within the "Couple_Index" table there is a field called "ProtocolIndexID" which is a foreign key within this table. The same field is in the "Protocol_Index" table as a primary key. Like I said above, when the data is all there, meaning I have to manually input this in the field "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when the user first inputs a new protocol number from the "Protocol_Index" form, that it automatically updates the "ProtocolIndexID" field in the "Couple_Index" table?
![]() |
0 |
![]() |
When using liked forms like this you can pass the value to the second form via theOpenArgs mechanism. The code behind the button on the Protocol_Index form would be along these lines: Dim strCriteria As String ' first ensure current record is saved as ' if it’s a new record, then trying to insert ' a new row into the referencing table ' would violate referential integrity if ' the record in the referenced table ' had not been saved Me.Dirty = False strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID ' open Couple_Index form in dialogue mode ' and pass current ProtocolIndexID to it DoCmd.OpenForm " Couple_Index", _ WhereCondtion:=strCriteria, _ WindowMode:=acDialog, _ OpenArgs:=Me.ProtocolIndexID The above assumes that ProtocolIndexID is a number data type. If it’s a text data type use: strCriteria = " ProtocolIndexID = """ & Me.ProtocolIndexID & """" In the Couple_Index form's Open event procedure set the DefaultValue property of the ProtocolIndexID control to the value (if any) of the OpenArgs property: If Not IsNull(Me.OpenArgs) Then Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" End If Note that the DefaultValue property is always a string expression regardless of the data type, so should be wrapped in quotes characters as above. Often this isn't crucial, but sometimes it is (dates are a case in point) so its prudent to do so regardless. BTW if you are interested in more flexible uses of the OpenArgs property, such as passing value lists or named arguments, I've posted a demo of a module developed some years ago by Stuart McCall and myself for doing this at: http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=24091&webtag=ws-msdevapps Another way of doing this would be to use a tab control on a form. Put the Protocol_Index controls on the first page of the tab control and a subform (you can use your current Couple_Index form for this) on the second page, setting the subform control's LinkMasterFields and LinkChildFields properties to ProtocolIndexID. All you need to do is then tab to the second page. Any new records in the subform will automatically have the ProtocolIndexID value inserted via the linking mechanism with no code needed whatsoever. If you wished, and there is enough space to do so, you could do without the tab control and have both the Protocol_Index record and the subform containing the related records visible simultaneously. Ken Sheridan Stafford, England "Mike" wrote: > I have a form that user inputs a protocol number into. This form is called > "Protocol_Index". When the user first inputs this protocol number, right > after they have to to another form, which they easily do by pressing a button > that takes them there. However when I have all the data in the tables for > both "Couple_Index" and "Protocol_Index" this seems to work fine. What > happens is when they are searching for a record based on a protocol number in > the "Protocol_Index" form they simply press a button to take them to the > "Couple_Index" form. Normally the couples information is there to see based > on the protocol number from the previous form (Protocol_Index). But this > only happens when all data is manaully changed. If I'm creating a new > record, this doesnt work. > > Within the "Couple_Index" table there is a field called "ProtocolIndexID" > which is a foreign key within this table. The same field is in the > "Protocol_Index" table as a primary key. Like I said above, when the data is > all there, meaning I have to manually input this in the field > "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when > the user first inputs a new protocol number from the "Protocol_Index" form, > that it automatically updates the "ProtocolIndexID" field in the > "Couple_Index" table?
![]() |
0 |
![]() |
Hey Ken, thanks for responding to my issue. You'll have to forgive me if I dont understand something that may seem easy. I've got down your first part as far as using the VBA code within the button that opens the "Couple_Index" form. However I'm not exactly sure where to place the second VBA part. You said to place it in the: In the Couple_Index form's Open event procedure set the DefaultValue > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > property: I'm not exactly sure where I could find that. Would you mean the properties for the text box that shows the foreign key? Michael "Ken Sheridan" wrote: > When using liked forms like this you can pass the value to the second form > via theOpenArgs mechanism. The code behind the button on the Protocol_Index > form would be along these lines: > > Dim strCriteria As String > > ' first ensure current record is saved as > ' if it’s a new record, then trying to insert > ' a new row into the referencing table > ' would violate referential integrity if > ' the record in the referenced table > ' had not been saved > Me.Dirty = False > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > ' open Couple_Index form in dialogue mode > ' and pass current ProtocolIndexID to it > DoCmd.OpenForm " Couple_Index", _ > WhereCondtion:=strCriteria, _ > WindowMode:=acDialog, _ > OpenArgs:=Me.ProtocolIndexID > > The above assumes that ProtocolIndexID is a number data type. If it’s a > text data type use: > > strCriteria = " ProtocolIndexID = """ & Me.ProtocolIndexID & """" > > In the Couple_Index form's Open event procedure set the DefaultValue > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > property: > > If Not IsNull(Me.OpenArgs) Then > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > End If > > Note that the DefaultValue property is always a string expression regardless > of the data type, so should be wrapped in quotes characters as above. Often > this isn't crucial, but sometimes it is (dates are a case in point) so its > prudent to do so regardless. > > BTW if you are interested in more flexible uses of the OpenArgs property, > such as passing value lists or named arguments, I've posted a demo of a > module developed some years ago by Stuart McCall and myself for doing this at: > > > http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=24091&webtag=ws-msdevapps > > > Another way of doing this would be to use a tab control on a form. Put the > Protocol_Index controls on the first page of the tab control and a subform > (you can use your current Couple_Index form for this) on the second page, > setting the subform control's LinkMasterFields and LinkChildFields properties > to ProtocolIndexID. All you need to do is then tab to the second page. Any > new records in the subform will automatically have the ProtocolIndexID value > inserted via the linking mechanism with no code needed whatsoever. If you > wished, and there is enough space to do so, you could do without the tab > control and have both the Protocol_Index record and the subform containing > the related records visible simultaneously. > > Ken Sheridan > Stafford, England > > "Mike" wrote: > > > I have a form that user inputs a protocol number into. This form is called > > "Protocol_Index". When the user first inputs this protocol number, right > > after they have to to another form, which they easily do by pressing a button > > that takes them there. However when I have all the data in the tables for > > both "Couple_Index" and "Protocol_Index" this seems to work fine. What > > happens is when they are searching for a record based on a protocol number in > > the "Protocol_Index" form they simply press a button to take them to the > > "Couple_Index" form. Normally the couples information is there to see based > > on the protocol number from the previous form (Protocol_Index). But this > > only happens when all data is manaully changed. If I'm creating a new > > record, this doesnt work. > > > > Within the "Couple_Index" table there is a field called "ProtocolIndexID" > > which is a foreign key within this table. The same field is in the > > "Protocol_Index" table as a primary key. Like I said above, when the data is > > all there, meaning I have to manually input this in the field > > "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when > > the user first inputs a new protocol number from the "Protocol_Index" form, > > that it automatically updates the "ProtocolIndexID" field in the > > "Couple_Index" table? >
![]() |
0 |
![]() |
I assume its the following code you are having problems with: If Not IsNull(Me.OpenArgs) Then Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" End If This goes in the Open event procedure of the Couple_Index form. To do this open the form in design view. In the Events tab of the form's properties sheet select the On Open event property. Click the 'build' button; that's the one with the ellipsis (3 dots) to the right. Select Code Builder in the dialogue and when the VBA window opens at the Open event property add the code as new lines between the two already in place. Ken Sheridan Stafford, England "Mike" wrote: > Hey Ken, thanks for responding to my issue. You'll have to forgive me if I > dont understand something that may seem easy. I've got down your first part > as far as using the VBA code within the button that opens the "Couple_Index" > form. However I'm not exactly sure where to place the second VBA part. You > said to place it in the: > > In the Couple_Index form's Open event procedure set the DefaultValue > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > property: > > I'm not exactly sure where I could find that. Would you mean the properties > for the text box that shows the foreign key? > > Michael > > "Ken Sheridan" wrote: > > > When using liked forms like this you can pass the value to the second form > > via theOpenArgs mechanism. The code behind the button on the Protocol_Index > > form would be along these lines: > > > > Dim strCriteria As String > > > > ' first ensure current record is saved as > > ' if it’s a new record, then trying to insert > > ' a new row into the referencing table > > ' would violate referential integrity if > > ' the record in the referenced table > > ' had not been saved > > Me.Dirty = False > > > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > > > ' open Couple_Index form in dialogue mode > > ' and pass current ProtocolIndexID to it > > DoCmd.OpenForm " Couple_Index", _ > > WhereCondtion:=strCriteria, _ > > WindowMode:=acDialog, _ > > OpenArgs:=Me.ProtocolIndexID > > > > The above assumes that ProtocolIndexID is a number data type. If it’s a > > text data type use: > > > > strCriteria = " ProtocolIndexID = """ & Me.ProtocolIndexID & """" > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > property: > > > > If Not IsNull(Me.OpenArgs) Then > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > End If > > > > Note that the DefaultValue property is always a string expression regardless > > of the data type, so should be wrapped in quotes characters as above. Often > > this isn't crucial, but sometimes it is (dates are a case in point) so its > > prudent to do so regardless. > > > > BTW if you are interested in more flexible uses of the OpenArgs property, > > such as passing value lists or named arguments, I've posted a demo of a > > module developed some years ago by Stuart McCall and myself for doing this at: > > > > > > http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=24091&webtag=ws-msdevapps > > > > > > Another way of doing this would be to use a tab control on a form. Put the > > Protocol_Index controls on the first page of the tab control and a subform > > (you can use your current Couple_Index form for this) on the second page, > > setting the subform control's LinkMasterFields and LinkChildFields properties > > to ProtocolIndexID. All you need to do is then tab to the second page. Any > > new records in the subform will automatically have the ProtocolIndexID value > > inserted via the linking mechanism with no code needed whatsoever. If you > > wished, and there is enough space to do so, you could do without the tab > > control and have both the Protocol_Index record and the subform containing > > the related records visible simultaneously. > > > > Ken Sheridan > > Stafford, England > > > > "Mike" wrote: > > > > > I have a form that user inputs a protocol number into. This form is called > > > "Protocol_Index". When the user first inputs this protocol number, right > > > after they have to to another form, which they easily do by pressing a button > > > that takes them there. However when I have all the data in the tables for > > > both "Couple_Index" and "Protocol_Index" this seems to work fine. What > > > happens is when they are searching for a record based on a protocol number in > > > the "Protocol_Index" form they simply press a button to take them to the > > > "Couple_Index" form. Normally the couples information is there to see based > > > on the protocol number from the previous form (Protocol_Index). But this > > > only happens when all data is manaully changed. If I'm creating a new > > > record, this doesnt work. > > > > > > Within the "Couple_Index" table there is a field called "ProtocolIndexID" > > > which is a foreign key within this table. The same field is in the > > > "Protocol_Index" table as a primary key. Like I said above, when the data is > > > all there, meaning I have to manually input this in the field > > > "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when > > > the user first inputs a new protocol number from the "Protocol_Index" form, > > > that it automatically updates the "ProtocolIndexID" field in the > > > "Couple_Index" table? > >
![]() |
0 |
![]() |
Hey Ken I really appreciate you help and the fact that you're taking the time to be patient with me. This is whats happening. After following all of your instructions, when I'm on the "Protocol_Index" form and I click on the button that pulls up the "Couple_Index" form I recieve a Compile Error: Named Argument not found. This is the current code that lies behind the button: Private Sub Command56_Click() On Error GoTo Err_Command56_Click Dim stDocName As String Dim stLinkCriteria As String Dim strCriteria As String stDocName = "Couple_IndexFrm" stLinkCriteria = "[ProtocolIndexID]=" & Me![Protocol Number] DoCmd.OpenForm stDocName, , , stLinkCriteria Me.Dirty = False strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID DoCmd.OpenForm " Couple_Index", _ WhereCondtion:=strCriteria, _ WindowMode:=acDialog, _ OpenArgs:=Me.ProtocolIndexID Exit_Command56_Click: Exit Sub Err_Command56_Click: MsgBox Err.Description Resume Exit_Command56_Click End Sub Any suggestions? "Ken Sheridan" wrote: > I assume its the following code you are having problems with: > > If Not IsNull(Me.OpenArgs) Then > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > End If > > This goes in the Open event procedure of the Couple_Index form. To do this > open the form in design view. In the Events tab of the form's properties > sheet select the On Open event property. Click the 'build' button; that's > the one with the ellipsis (3 dots) to the right. Select Code Builder in the > dialogue and when the VBA window opens at the Open event property add the > code as new lines between the two already in place. > > Ken Sheridan > Stafford, England > > "Mike" wrote: > > > Hey Ken, thanks for responding to my issue. You'll have to forgive me if I > > dont understand something that may seem easy. I've got down your first part > > as far as using the VBA code within the button that opens the "Couple_Index" > > form. However I'm not exactly sure where to place the second VBA part. You > > said to place it in the: > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > property: > > > > I'm not exactly sure where I could find that. Would you mean the properties > > for the text box that shows the foreign key? > > > > Michael > > > > "Ken Sheridan" wrote: > > > > > When using liked forms like this you can pass the value to the second form > > > via theOpenArgs mechanism. The code behind the button on the Protocol_Index > > > form would be along these lines: > > > > > > Dim strCriteria As String > > > > > > ' first ensure current record is saved as > > > ' if it’s a new record, then trying to insert > > > ' a new row into the referencing table > > > ' would violate referential integrity if > > > ' the record in the referenced table > > > ' had not been saved > > > Me.Dirty = False > > > > > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > > > > > ' open Couple_Index form in dialogue mode > > > ' and pass current ProtocolIndexID to it > > > DoCmd.OpenForm " Couple_Index", _ > > > WhereCondtion:=strCriteria, _ > > > WindowMode:=acDialog, _ > > > OpenArgs:=Me.ProtocolIndexID > > > > > > The above assumes that ProtocolIndexID is a number data type. If it’s a > > > text data type use: > > > > > > strCriteria = " ProtocolIndexID = """ & Me.ProtocolIndexID & """" > > > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > property: > > > > > > If Not IsNull(Me.OpenArgs) Then > > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > > End If > > > > > > Note that the DefaultValue property is always a string expression regardless > > > of the data type, so should be wrapped in quotes characters as above. Often > > > this isn't crucial, but sometimes it is (dates are a case in point) so its > > > prudent to do so regardless. > > > > > > BTW if you are interested in more flexible uses of the OpenArgs property, > > > such as passing value lists or named arguments, I've posted a demo of a > > > module developed some years ago by Stuart McCall and myself for doing this at: > > > > > > > > > http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=24091&webtag=ws-msdevapps > > > > > > > > > Another way of doing this would be to use a tab control on a form. Put the > > > Protocol_Index controls on the first page of the tab control and a subform > > > (you can use your current Couple_Index form for this) on the second page, > > > setting the subform control's LinkMasterFields and LinkChildFields properties > > > to ProtocolIndexID. All you need to do is then tab to the second page. Any > > > new records in the subform will automatically have the ProtocolIndexID value > > > inserted via the linking mechanism with no code needed whatsoever. If you > > > wished, and there is enough space to do so, you could do without the tab > > > control and have both the Protocol_Index record and the subform containing > > > the related records visible simultaneously. > > > > > > Ken Sheridan > > > Stafford, England > > > > > > "Mike" wrote: > > > > > > > I have a form that user inputs a protocol number into. This form is called > > > > "Protocol_Index". When the user first inputs this protocol number, right > > > > after they have to to another form, which they easily do by pressing a button > > > > that takes them there. However when I have all the data in the tables for > > > > both "Couple_Index" and "Protocol_Index" this seems to work fine. What > > > > happens is when they are searching for a record based on a protocol number in > > > > the "Protocol_Index" form they simply press a button to take them to the > > > > "Couple_Index" form. Normally the couples information is there to see based > > > > on the protocol number from the previous form (Protocol_Index). But this > > > > only happens when all data is manaully changed. If I'm creating a new > > > > record, this doesnt work. > > > > > > > > Within the "Couple_Index" table there is a field called "ProtocolIndexID" > > > > which is a foreign key within this table. The same field is in the > > > > "Protocol_Index" table as a primary key. Like I said above, when the data is > > > > all there, meaning I have to manually input this in the field > > > > "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when > > > > the user first inputs a new protocol number from the "Protocol_Index" form, > > > > that it automatically updates the "ProtocolIndexID" field in the > > > > "Couple_Index" table? > > > >
![]() |
0 |
![]() |
Partly a typo on my part (missing i in WhereCondition), partly duplication of code by you. It should be like this: Private Sub Command56_Click() On Error GoTo Err_Command56_Click Dim strDocName As String Dim strCriteria As String strDocName = "Couple_IndexFrm" strCriteria = "ProtocolIndexID=" & Me.[Protocol Number] Me.Dirty = False DoCmd.OpenForm strDocName, _ WhereCondition:=strCriteria, _ WindowMode:=acDialog, _ OpenArgs:=Me.ProtocolIndexID Exit_Command56_Click: Exit Sub Err_Command56_Click: MsgBox Err.Description Resume Exit_Command56_Click End Sub Ken Sheridan Stafford, England "Mike" wrote: > Hey Ken I really appreciate you help and the fact that you're taking the time > to be patient with me. This is whats happening. After following all of your > instructions, when I'm on the "Protocol_Index" form and I click on the button > that pulls up the "Couple_Index" form I recieve a Compile Error: Named > Argument not found. > > This is the current code that lies behind the button: > > Private Sub Command56_Click() > On Error GoTo Err_Command56_Click > > Dim stDocName As String > Dim stLinkCriteria As String > Dim strCriteria As String > > stDocName = "Couple_IndexFrm" > > stLinkCriteria = "[ProtocolIndexID]=" & Me![Protocol Number] > DoCmd.OpenForm stDocName, , , stLinkCriteria > > > Me.Dirty = False > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > DoCmd.OpenForm " Couple_Index", _ > WhereCondtion:=strCriteria, _ > WindowMode:=acDialog, _ > OpenArgs:=Me.ProtocolIndexID > > Exit_Command56_Click: > Exit Sub > > Err_Command56_Click: > MsgBox Err.Description > Resume Exit_Command56_Click > > End Sub > > Any suggestions? > > "Ken Sheridan" wrote: > > > I assume its the following code you are having problems with: > > > > If Not IsNull(Me.OpenArgs) Then > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > End If > > > > This goes in the Open event procedure of the Couple_Index form. To do this > > open the form in design view. In the Events tab of the form's properties > > sheet select the On Open event property. Click the 'build' button; that's > > the one with the ellipsis (3 dots) to the right. Select Code Builder in the > > dialogue and when the VBA window opens at the Open event property add the > > code as new lines between the two already in place. > > > > Ken Sheridan > > Stafford, England > > > > "Mike" wrote: > > > > > Hey Ken, thanks for responding to my issue. You'll have to forgive me if I > > > dont understand something that may seem easy. I've got down your first part > > > as far as using the VBA code within the button that opens the "Couple_Index" > > > form. However I'm not exactly sure where to place the second VBA part. You > > > said to place it in the: > > > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > > property: > > > > > > I'm not exactly sure where I could find that. Would you mean the properties > > > for the text box that shows the foreign key? > > > > > > Michael > > > > > > "Ken Sheridan" wrote: > > > > > > > When using liked forms like this you can pass the value to the second form > > > > via theOpenArgs mechanism. The code behind the button on the Protocol_Index > > > > form would be along these lines: > > > > > > > > Dim strCriteria As String > > > > > > > > ' first ensure current record is saved as > > > > ' if it’s a new record, then trying to insert > > > > ' a new row into the referencing table > > > > ' would violate referential integrity if > > > > ' the record in the referenced table > > > > ' had not been saved > > > > Me.Dirty = False > > > > > > > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > > > > > > > ' open Couple_Index form in dialogue mode > > > > ' and pass current ProtocolIndexID to it > > > > DoCmd.OpenForm " Couple_Index", _ > > > > WhereCondtion:=strCriteria, _ > > > > WindowMode:=acDialog, _ > > > > OpenArgs:=Me.ProtocolIndexID > > > > > > > > The above assumes that ProtocolIndexID is a number data type. If it’s a > > > > text data type use: > > > > > > > > strCriteria = " ProtocolIndexID = """ & Me.ProtocolIndexID & """" > > > > > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > > property: > > > > > > > > If Not IsNull(Me.OpenArgs) Then > > > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > > > End If > > > > > > > > Note that the DefaultValue property is always a string expression regardless > > > > of the data type, so should be wrapped in quotes characters as above. Often > > > > this isn't crucial, but sometimes it is (dates are a case in point) so its > > > > prudent to do so regardless. > > > > > > > > BTW if you are interested in more flexible uses of the OpenArgs property, > > > > such as passing value lists or named arguments, I've posted a demo of a > > > > module developed some years ago by Stuart McCall and myself for doing this at: > > > > > > > > > > > > http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=24091&webtag=ws-msdevapps > > > > > > > > > > > > Another way of doing this would be to use a tab control on a form. Put the > > > > Protocol_Index controls on the first page of the tab control and a subform > > > > (you can use your current Couple_Index form for this) on the second page, > > > > setting the subform control's LinkMasterFields and LinkChildFields properties > > > > to ProtocolIndexID. All you need to do is then tab to the second page. Any > > > > new records in the subform will automatically have the ProtocolIndexID value > > > > inserted via the linking mechanism with no code needed whatsoever. If you > > > > wished, and there is enough space to do so, you could do without the tab > > > > control and have both the Protocol_Index record and the subform containing > > > > the related records visible simultaneously. > > > > > > > > Ken Sheridan > > > > Stafford, England > > > > > > > > "Mike" wrote: > > > > > > > > > I have a form that user inputs a protocol number into. This form is called > > > > > "Protocol_Index". When the user first inputs this protocol number, right > > > > > after they have to to another form, which they easily do by pressing a button > > > > > that takes them there. However when I have all the data in the tables for > > > > > both "Couple_Index" and "Protocol_Index" this seems to work fine. What > > > > > happens is when they are searching for a record based on a protocol number in > > > > > the "Protocol_Index" form they simply press a button to take them to the > > > > > "Couple_Index" form. Normally the couples information is there to see based > > > > > on the protocol number from the previous form (Protocol_Index). But this > > > > > only happens when all data is manaully changed. If I'm creating a new > > > > > record, this doesnt work. > > > > > > > > > > Within the "Couple_Index" table there is a field called "ProtocolIndexID" > > > > > which is a foreign key within this table. The same field is in the > > > > > "Protocol_Index" table as a primary key. Like I said above, when the data is > > > > > all there, meaning I have to manually input this in the field > > > > > "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when > > > > > the user first inputs a new protocol number from the "Protocol_Index" form, > > > > > that it automatically updates the "ProtocolIndexID" field in the > > > > > "Couple_Index" table? > > > > > > do it.
![]() |
0 |
![]() |
Hey Ken! It seems to be working as far as putting the newly created Protocol Number in the "ProtocolIndexID" field within the "Couple_Index" table. However now the button (Couples button on "Protocol_IndexFrm") the way it use to. At any given protocol number showing on the "Protocol_IndexFrm" it would immediately go to the related record on the "Couple_IndexFrm" when selected. Is this something that cannot be compromised or can it work with that option as well? If so, how? Thanks again for all your help! "Ken Sheridan" wrote: > Partly a typo on my part (missing i in WhereCondition), partly duplication of > code by you. It should be like this: > > Private Sub Command56_Click() > On Error GoTo Err_Command56_Click > > Dim strDocName As String > Dim strCriteria As String > > strDocName = "Couple_IndexFrm" > strCriteria = "ProtocolIndexID=" & Me.[Protocol Number] > > Me.Dirty = False > > DoCmd.OpenForm strDocName, _ > WhereCondition:=strCriteria, _ > WindowMode:=acDialog, _ > OpenArgs:=Me.ProtocolIndexID > > Exit_Command56_Click: > Exit Sub > > Err_Command56_Click: > MsgBox Err.Description > Resume Exit_Command56_Click > > End Sub > > > Ken Sheridan > Stafford, England > > "Mike" wrote: > > > Hey Ken I really appreciate you help and the fact that you're taking the time > > to be patient with me. This is whats happening. After following all of your > > instructions, when I'm on the "Protocol_Index" form and I click on the button > > that pulls up the "Couple_Index" form I recieve a Compile Error: Named > > Argument not found. > > > > This is the current code that lies behind the button: > > > > Private Sub Command56_Click() > > On Error GoTo Err_Command56_Click > > > > Dim stDocName As String > > Dim stLinkCriteria As String > > Dim strCriteria As String > > > > stDocName = "Couple_IndexFrm" > > > > stLinkCriteria = "[ProtocolIndexID]=" & Me![Protocol Number] > > DoCmd.OpenForm stDocName, , , stLinkCriteria > > > > > > Me.Dirty = False > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > DoCmd.OpenForm " Couple_Index", _ > > WhereCondtion:=strCriteria, _ > > WindowMode:=acDialog, _ > > OpenArgs:=Me.ProtocolIndexID > > > > Exit_Command56_Click: > > Exit Sub > > > > Err_Command56_Click: > > MsgBox Err.Description > > Resume Exit_Command56_Click > > > > End Sub > > > > Any suggestions? > > > > "Ken Sheridan" wrote: > > > > > I assume its the following code you are having problems with: > > > > > > If Not IsNull(Me.OpenArgs) Then > > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > > End If > > > > > > This goes in the Open event procedure of the Couple_Index form. To do this > > > open the form in design view. In the Events tab of the form's properties > > > sheet select the On Open event property. Click the 'build' button; that's > > > the one with the ellipsis (3 dots) to the right. Select Code Builder in the > > > dialogue and when the VBA window opens at the Open event property add the > > > code as new lines between the two already in place. > > > > > > Ken Sheridan > > > Stafford, England > > > > > > "Mike" wrote: > > > > > > > Hey Ken, thanks for responding to my issue. You'll have to forgive me if I > > > > dont understand something that may seem easy. I've got down your first part > > > > as far as using the VBA code within the button that opens the "Couple_Index" > > > > form. However I'm not exactly sure where to place the second VBA part. You > > > > said to place it in the: > > > > > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > > > property: > > > > > > > > I'm not exactly sure where I could find that. Would you mean the properties > > > > for the text box that shows the foreign key? > > > > > > > > Michael > > > > > > > > "Ken Sheridan" wrote: > > > > > > > > > When using liked forms like this you can pass the value to the second form > > > > > via theOpenArgs mechanism. The code behind the button on the Protocol_Index > > > > > form would be along these lines: > > > > > > > > > > Dim strCriteria As String > > > > > > > > > > ' first ensure current record is saved as > > > > > ' if it’s a new record, then trying to insert > > > > > ' a new row into the referencing table > > > > > ' would violate referential integrity if > > > > > ' the record in the referenced table > > > > > ' had not been saved > > > > > Me.Dirty = False > > > > > > > > > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > > > > > > > > > ' open Couple_Index form in dialogue mode > > > > > ' and pass current ProtocolIndexID to it > > > > > DoCmd.OpenForm " Couple_Index", _ > > > > > WhereCondtion:=strCriteria, _ > > > > > WindowMode:=acDialog, _ > > > > > OpenArgs:=Me.ProtocolIndexID > > > > > > > > > > The above assumes that ProtocolIndexID is a number data type. If it’s a > > > > > text data type use: > > > > > > > > > > strCriteria = " ProtocolIndexID = """ & Me.ProtocolIndexID & """" > > > > > > > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > > > property: > > > > > > > > > > If Not IsNull(Me.OpenArgs) Then > > > > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > > > > End If > > > > > > > > > > Note that the DefaultValue property is always a string expression regardless > > > > > of the data type, so should be wrapped in quotes characters as above. Often > > > > > this isn't crucial, but sometimes it is (dates are a case in point) so its > > > > > prudent to do so regardless. > > > > > > > > > > BTW if you are interested in more flexible uses of the OpenArgs property, > > > > > such as passing value lists or named arguments, I've posted a demo of a > > > > > module developed some years ago by Stuart McCall and myself for doing this at: > > > > > > > > > > > > > > > http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=24091&webtag=ws-msdevapps > > > > > > > > > > > > > > > Another way of doing this would be to use a tab control on a form. Put the > > > > > Protocol_Index controls on the first page of the tab control and a subform > > > > > (you can use your current Couple_Index form for this) on the second page, > > > > > setting the subform control's LinkMasterFields and LinkChildFields properties > > > > > to ProtocolIndexID. All you need to do is then tab to the second page. Any > > > > > new records in the subform will automatically have the ProtocolIndexID value > > > > > inserted via the linking mechanism with no code needed whatsoever. If you > > > > > wished, and there is enough space to do so, you could do without the tab > > > > > control and have both the Protocol_Index record and the subform containing > > > > > the related records visible simultaneously. > > > > > > > > > > Ken Sheridan > > > > > Stafford, England > > > > > > > > > > "Mike" wrote: > > > > > > > > > > > I have a form that user inputs a protocol number into. This form is called > > > > > > "Protocol_Index". When the user first inputs this protocol number, right > > > > > > after they have to to another form, which they easily do by pressing a button > > > > > > that takes them there. However when I have all the data in the tables for > > > > > > both "Couple_Index" and "Protocol_Index" this seems to work fine. What > > > > > > happens is when they are searching for a record based on a protocol number in > > > > > > the "Protocol_Index" form they simply press a button to take them to the > > > > > > "Couple_Index" form. Normally the couples information is there to see based > > > > > > on the protocol number from the previous form (Protocol_Index). But this > > > > > > only happens when all data is manaully changed. If I'm creating a new > > > > > > record, this doesnt work. > > > > > > > > > > > > Within the "Couple_Index" table there is a field called "ProtocolIndexID" > > > > > > which is a foreign key within this table. The same field is in the > > > > > > "Protocol_Index" table as a primary key. Like I said above, when the data is > > > > > > all there, meaning I have to manually input this in the field > > > > > > "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when > > > > > > the user first inputs a new protocol number from the "Protocol_Index" form, > > > > > > that it automatically updates the "ProtocolIndexID" field in the > > > > > > "Couple_Index" table? > > > > > > > > do it. >
![]() |
0 |
![]() |
It should be doing that already. The WhereCondition setting of the OpenForm method sets the Filter property of the form being opened and applies the filter, so if any records exist in the Couple_Index table with a ProtocolIndexID which matches the current Protocol Number the form should open filtered to those records. If there are no records then it will be at a new record and the DefaultValue property of the ProtocolIndexID control will be set to the current Protocol Number. If only the latter is happening then the only reason I can think of for this would be if the Couple_IndexFrm form's DataEntry property is set to True (Yes in the form's properties sheet). If this property is True it limits the form to the entry of new records, so existing ones don't show. Check this out and if it is, set it to False (No). Ken Sheridan Stafford, England "Mike" wrote: > Hey Ken! It seems to be working as far as putting the newly created Protocol > Number in the "ProtocolIndexID" field within the "Couple_Index" table. > However now the button (Couples button on "Protocol_IndexFrm") the way it use > to. At any given protocol number showing on the "Protocol_IndexFrm" it would > immediately go to the related record on the "Couple_IndexFrm" when selected. > Is this something that cannot be compromised or can it work with that option > as well? If so, how? Thanks again for all your help! > > "Ken Sheridan" wrote: > > > Partly a typo on my part (missing i in WhereCondition), partly duplication of > > code by you. It should be like this: > > > > Private Sub Command56_Click() > > On Error GoTo Err_Command56_Click > > > > Dim strDocName As String > > Dim strCriteria As String > > > > strDocName = "Couple_IndexFrm" > > strCriteria = "ProtocolIndexID=" & Me.[ Protocol Number] > > > > Me.Dirty = False > > > > DoCmd.OpenForm strDocName, _ > > WhereCondition:=strCriteria, _ > > WindowMode:=acDialog, _ > > OpenArgs:=Me.ProtocolIndexID > > > > Exit_Command56_Click: > > Exit Sub > > > > Err_Command56_Click: > > MsgBox Err.Description > > Resume Exit_Command56_Click > > > > End Sub > > > > > > Ken Sheridan > > Stafford, England > > > > "Mike" wrote: > > > > > Hey Ken I really appreciate you help and the fact that you're taking the time > > > to be patient with me. This is whats happening. After following all of your > > > instructions, when I'm on the "Protocol_Index" form and I click on the button > > > that pulls up the "Couple_Index" form I recieve a Compile Error: Named > > > Argument not found. > > > > > > This is the current code that lies behind the button: > > > > > > Private Sub Command56_Click() > > > On Error GoTo Err_Command56_Click > > > > > > Dim stDocName As String > > > Dim stLinkCriteria As String > > > Dim strCriteria As String > > > > > > stDocName = "Couple_IndexFrm" > > > > > > stLinkCriteria = "[ProtocolIndexID]=" & Me![Protocol Number] > > > DoCmd.OpenForm stDocName, , , stLinkCriteria > > > > > > > > > Me.Dirty = False > > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > > DoCmd.OpenForm " Couple_Index", _ > > > WhereCondtion:=strCriteria, _ > > > WindowMode:=acDialog, _ > > > OpenArgs:=Me.ProtocolIndexID > > > > > > Exit_Command56_Click: > > > Exit Sub > > > > > > Err_Command56_Click: > > > MsgBox Err.Description > > > Resume Exit_Command56_Click > > > > > > End Sub > > > > > > Any suggestions? > > > > > > "Ken Sheridan" wrote: > > > > > > > I assume its the following code you are having problems with: > > > > > > > > If Not IsNull(Me.OpenArgs) Then > > > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > > > End If > > > > > > > > This goes in the Open event procedure of the Couple_Index form. To do this > > > > open the form in design view. In the Events tab of the form's properties > > > > sheet select the On Open event property. Click the 'build' button; that's > > > > the one with the ellipsis (3 dots) to the right. Select Code Builder in the > > > > dialogue and when the VBA window opens at the Open event property add the > > > > code as new lines between the two already in place. > > > > > > > > Ken Sheridan > > > > Stafford, England > > > > > > > > "Mike" wrote: > > > > > > > > > Hey Ken, thanks for responding to my issue. You'll have to forgive me if I > > > > > dont understand something that may seem easy. I've got down your first part > > > > > as far as using the VBA code within the button that opens the "Couple_Index" > > > > > form. However I'm not exactly sure where to place the second VBA part. You > > > > > said to place it in the: > > > > > > > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > > > > property: > > > > > > > > > > I'm not exactly sure where I could find that. Would you mean the properties > > > > > for the text box that shows the foreign key? > > > > > > > > > > Michael > > > > > > > > > > "Ken Sheridan" wrote: > > > > > > > > > > > When using liked forms like this you can pass the value to the second form > > > > > > via theOpenArgs mechanism. The code behind the button on the Protocol_Index > > > > > > form would be along these lines: > > > > > > > > > > > > Dim strCriteria As String > > > > > > > > > > > > ' first ensure current record is saved as > > > > > > ' if it’s a new record, then trying to insert > > > > > > ' a new row into the referencing table > > > > > > ' would violate referential integrity if > > > > > > ' the record in the referenced table > > > > > > ' had not been saved > > > > > > Me.Dirty = False > > > > > > > > > > > > strCriteria = " ProtocolIndexID = " & Me.ProtocolIndexID > > > > > > > > > > > > ' open Couple_Index form in dialogue mode > > > > > > ' and pass current ProtocolIndexID to it > > > > > > DoCmd.OpenForm " Couple_Index", _ > > > > > > WhereCondtion:=strCriteria, _ > > > > > > WindowMode:=acDialog, _ > > > > > > OpenArgs:=Me.ProtocolIndexID > > > > > > > > > > > > The above assumes that ProtocolIndexID is a number data type. If it’s a > > > > > > text data type use: > > > > > > > > > > > > strCriteria = " ProtocolIndexID = """ & Me.ProtocolIndexID & """" > > > > > > > > > > > > In the Couple_Index form's Open event procedure set the DefaultValue > > > > > > property of the ProtocolIndexID control to the value (if any) of the OpenArgs > > > > > > property: > > > > > > > > > > > > If Not IsNull(Me.OpenArgs) Then > > > > > > Me.ProtocolIndexID.DefaultValue = """" & Me.OpenArgs & """" > > > > > > End If > > > > > > > > > > > > Note that the DefaultValue property is always a string expression regardless > > > > > > of the data type, so should be wrapped in quotes characters as above. Often > > > > > > this isn't crucial, but sometimes it is (dates are a case in point) so its > > > > > > prudent to do so regardless. > > > > > > > > > > > > BTW if you are interested in more flexible uses of the OpenArgs property, > > > > > > such as passing value lists or named arguments, I've posted a demo of a > > > > > > module developed some years ago by Stuart McCall and myself for doing this at: > > > > > > > > > > > > > > > > > > http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=24091&webtag=ws-msdevapps > > > > > > > > > > > > > > > > > > Another way of doing this would be to use a tab control on a form. Put the > > > > > > Protocol_Index controls on the first page of the tab control and a subform > > > > > > (you can use your current Couple_Index form for this) on the second page, > > > > > > setting the subform control's LinkMasterFields and LinkChildFields properties > > > > > > to ProtocolIndexID. All you need to do is then tab to the second page. Any > > > > > > new records in the subform will automatically have the ProtocolIndexID value > > > > > > inserted via the linking mechanism with no code needed whatsoever. If you > > > > > > wished, and there is enough space to do so, you could do without the tab > > > > > > control and have both the Protocol_Index record and the subform containing > > > > > > the related records visible simultaneously. > > > > > > > > > > > > Ken Sheridan > > > > > > Stafford, England > > > > > > > > > > > > "Mike" wrote: > > > > > > > > > > > > > I have a form that user inputs a protocol number into. This form is called > > > > > > > "Protocol_Index". When the user first inputs this protocol number, right > > > > > > > after they have to to another form, which they easily do by pressing a button > > > > > > > that takes them there. However when I have all the data in the tables for > > > > > > > both "Couple_Index" and "Protocol_Index" this seems to work fine. What > > > > > > > happens is when they are searching for a record based on a protocol number in > > > > > > > the "Protocol_Index" form they simply press a button to take them to the > > > > > > > "Couple_Index" form. Normally the couples information is there to see based > > > > > > > on the protocol number from the previous form (Protocol_Index). But this > > > > > > > only happens when all data is manaully changed. If I'm creating a new > > > > > > > record, this doesnt work. > > > > > > > > > > > > > > Within the "Couple_Index" table there is a field called "ProtocolIndexID" > > > > > > > which is a foreign key within this table. The same field is in the > > > > > > > "Protocol_Index" table as a primary key. Like I said above, when the data is > > > > > > > all there, meaning I have to manually input this in the field > > > > > > > "ProtocolIndexID" within the "Couple_Index" table. Is there a way that when > > > > > > > the user first inputs a new protocol number from the "Protocol_Index" form, > > > > > > > that it automatically updates the "ProtocolIndexID" field in the > > > > > > > "Couple_Index" table? > > > > > > > > > > do it. > >
![]() |
0 |
![]() |