By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,487 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,487 IT Pros & Developers. It's quick & easy.

Linking a value to multiple records

P: 56
I am trying to get one value to return more than one linked field. The single link works. This is the code that works.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command27_Click()
  3. If IsNull(CboGolfer) Then
  6. Exit Sub
  7. End If
  10.     Dim stDocName As String
  11.     Dim stLinkCriteria As String
  13.     stDocName = "FrmTeamList"
  15.         stLinkCriteria = "[GOLFER1]=" & Me![CboGolfer]
  16.         DoCmd.OpenForm stDocName, , , stLinkCriteria
  18. Exit_Command27_Click:
  19.     Exit Sub
  21. Err_Command27_Click:
  22.     MsgBox Err.Description
  23.     Resume Exit_Command27_Click
  25. End Sub
I need to include [GOLFER2], [GOLFER3], and [GOLFER4]

How can I do this. I want to do "and/or" for each of these so that the form returns any record where the value shows up in any of these fields.

Jun 9 '09 #1
Share this Question
Share on Google+
14 Replies

Expert 2.5K+
P: 2,653
Hello, Desitech.

Always glad to help pointing you to "Database Normalization" article.

Kind regards,
Jun 9 '09 #2

Expert 100+
P: 904
You could just conecate the values to your string and seperate them with a comma, then pass that string like you are doing then in the open event of the form you can use the SPLIT function to seperate out all the values.

OR normalize the database.


Jun 9 '09 #3

P: 56
Do mean, like this.
Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria = "[GOLFER1],[GOLFER2],[GOLFER3],[GOLFER4]=" & Me![CboGolfer]
  2.         DoCmd.OpenForm stDocName, , , stLinkCriteria
How do I use the "SPLIT" function
Jun 9 '09 #4

Expert 100+
P: 1,287
If you conclude that your architecture is sound you can use:
Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria = "GOLFER1 = " & CboGolfer & " OR GOLFER2 = " & CboGolfer & " OR GOLFER3 = " etc.
Jun 9 '09 #5

Expert Mod 15k+
P: 31,709
Frankly what you're actually after is very unclear.

I suspect that the In() SQL function would help, but your question makes so little sense it's hard to be sure. Remember, dumping some code in the question is no substitute for asking a meaningful question.

Please take more care with your questions in future.

Jun 9 '09 #6

Expert 100+
P: 904
Assuming your stLinkCriteria consists something like this "Golpher1,Golpher2,Golpher3,etc."

Then in the form where the openargs got passed to you would split out those names like so

In the General Declarations
Expand|Select|Wrap|Line Numbers
  1. Public strGolpher1 as String
  2. Public strGolpher2 as String
  3. Public strGolpher3 as String

In the OPEN FORM event
Expand|Select|Wrap|Line Numbers
  2. Dim strArraySplit
  3. Dim strPassArg As String
  5. strPassArg = Me.OpenArgs
  7. strArraySplit = Split(strPassArg, ",")
  8. strGolpher1= strArraySplit(0)
  9. strGolpher2= strArraySplit(1)
  10. strGolpher3= strArraySplit(2)
Then you can use the golpher variables whereever you wish in this form.


Jun 9 '09 #7

Expert 100+
P: 1,287
The 4th argument to DoCmd.OpenForm is WhereCondition. If you choose to use OpenArgs, it is the 7th argument. Your post was unclear, do you want to search a team for 1 specific golfer, or search for multiple golfers?
Jun 9 '09 #8

Expert 100+
P: 904
Oops - misread the number of commas. Thank Chipr. (damn laser surgery - I knew I should have paid for the guy who had a real office and not working out of his truck)


Jun 9 '09 #9

P: 56
Cool that worked. Thanks for all your help. I have one last question.
I am opening a report where the golfer name appears on the report from the value in the combobox from my main form. The Golfer name comes from the table "TblGolfer", The value that is returned is from the GolferID Field rather than the GolferName Field of that table on the report. How do I get the name to appear instead? I am using this expression in the text box control source on the report: =Forms!FrmMain!CboGolfer.
Jun 9 '09 #10

Expert 100+
P: 904
Use the COLUMN(index) parameter for your combo box.


Jun 9 '09 #11

P: 56
I don't understand. Is this located under the properties of the combobox. If so, It isn't there. Or am I misunderstanding? Could you walk me through this?
Jun 9 '09 #12

Expert 100+
P: 1,287
Make sure that the source for your combo box includes both the GolfterID and the GolfterName. The bound column should be the GolferID, though you may make that column hidden if you want. The column index starts at zero, so you can refer to the name of the selected golfer with:
Expand|Select|Wrap|Line Numbers
  1. CboGolfer.Column(1)
Jun 9 '09 #13

Expert 100+
P: 1,287
Or, change the source of the text box on the report to:
Expand|Select|Wrap|Line Numbers
  1. =DLookup("GolferName", "TblGolfer", "[GolferID] = " & Forms!FrmMain!CboGolfer)
Jun 9 '09 #14

P: 56
Thanks the .Column(1) is all I needed. Works great now. Thanks a ton.
Jun 9 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.