Get Correct Value

[JackMack]

Form1 is bound to a table with two columns: Week_Number, Week_Date. Work_Number column has numbers 1 though 52. Week_Date has the date of each Saturday in the year. When a user clicks on a row, I want that "Click" to open Form2 and pass the Week_Number value to Form_2.

Form_2 has three columns: Week_Number, Home_Team_ID, and Away_Team_ID. When it opens, I want the "cursor" to be on the first row that corresponds to the passed value. Also, I want to display only those rows which match this value.

How do I make this happen?

Lastly, would this be harder or easier using a sub-form?

[Seth Schrock]

If there are multiple records in Form_2 that have the same Week_Number, then a subform would be the way to go. If there is only one record in Form_2 for each record in Form_1, then you could put them all on the same form using a query to combine the data or just combine all the fields into one table. But if you want them on separate forms, then something like

DoCmd.OpenForm "Form_2"
DoCmd.SearchForRecord "Week_Number = " & Me.Week_Number

I prefer this method as it doesn't set the form's filter.

[ADezii]

1. You can pass the Value of the [Week_Number] Field in Form1 to Form2 via the OpenArgs Property of Form1, namely:
   

   DoCmd.OpenForm "Form2", acNormal, , , acFormEdit, acWindowNormal, Me![Week_Number]

2. To then access this Value and act accordingly in Form2:
   

   Debug.Print Me.OpenArgs

[NeoPa]

I would use a main form displayed in Continuous View and link to a subform in Single View using the Week_Number from both forms.

[JackMack]

I created two forms. In the first form, I added

Private Sub Form_Click()
   DoCmd.OpenForm "All_Matches_Frm", , , , , , Me.WeekNmbr
End Sub

to the "On Click" property. However, it does NOT open the second Form. There is no error produced. It just seems to ignore the click. Anyone got any ideas?

[NeoPa]

Have you set the form up to use this code? Is the form's Click event set to "[Event Procedure]"?

[JackMack]

@NeoPa
Yes. And procedure is set to:

Private Sub Form_Click()
   DoCmd.OpenForm "All_Matches_Frm", , , "WeekNmbr = Me.WeekNmbr", , , "Me.WeekNmbr"
End Sub

[Seth Schrock]

Please use the [CODE/] button when posting code. It makes it a lot easier to read.

Not sure why your form isn't opening, especially if there are no errors. Unless it is opening in a hidden view somehow. Try putting the word Stop right before the DoCmd.OpenForm... just so that you know what you are clicking is triggering the form's OnClick event. If the Stop doesn't get highlighted in yellow (by default it is yellow anyway) with the code in break mode, then the event isn't triggering.

Also, your Where Condition is wrong. You need to make it be

"WeekNmbr = " & Me.WeekNmbr

You also don't need to use both the Where Condition and the Open Args. Using the Where condition opens the form with a filter applied. Using the Open Args lets you do a Me.FindNext in your second form's OnLoad event that will just find the record you want instead of placing a filter on the form. One is not better than the other just because, but each has its own purpose and you just need to decide which you want.

[Rabbit]

You're using the form click event? I don't think that triggers just by clicking anywhere on a form. It has to be record selector or a blank area on the form. If you're actually clicking on a control, you will need to use that control's click event.

[JackMack]

I am clicking on one of the rows displayed in the form.

Seth, Thx for the reply. At this point, I'd be happy to just be able to get the second form to open by clicking in the first form.

[Seth Schrock]

Like Rabbit, I'm not sure exactly where you have to click to get the form's OnClick event to run. So, just for now, try adding a button to your form and we can use its OnClick event so that we know that the event is triggering. Just put the code that you have in the button's OnClick event. Then click the button and tell us what happens. If you get an error message, we need the number and the exact wording of it.

Also, read the following link, paying particular attention to the first section (A. For VBA code specifically) and make sure that you have Option Explicit set and that you can compile your code without errors: Before Posting (VBA or SQL) Code. This could help us find some of the problems.

[JackMack]

My Visual Basic for Access does not seem to have a compile option. but I created the button as you suggested and it DID IN FACT open the second form. And it used the WeekNmbr value to select only those rows that matched. Thank you all so much for your help. Should I leave well enough alone, or should I continue my efforts to make clicking on a row open the second form?

Have any of you ever made a form that displayed the rows from a table and when you clicked on any particular row, it opened another form and passed the row data from the first form?

[Seth Schrock]

That is really up to you. You just need to figure out what tiggers the form's OnClick event. There must be a certain part of the form that triggers it, but I'm not sure which part. Is your form's Record Selectors property set to Yes or No? If yes, then you should see a gray bar on the left side of the form if you are in Form view and just left of the first field if you are in datasheet view. Either way, try clicking it and see if the event triggers.

[zmbd]

JackMack: Did you look for the compiler under the [Debug] tools in the ribbon?

[JackMack]

No. I didn't. But after reading your note, I tried that and sure enuf, there it is. Thx for the tip.

[Rabbit]

The form click event only triggers when clicking on the record selector or a blank area on the form. When you say you're clicking on a "row", you are probably clicking on a control. That triggers the control's click event. It does not trigger the form's click event.