Setfocus to the combobox if a certain criteria is met

[nhdee]

The code below works but ONLY if I manually highlight the row where "Gift Certificate" has been selected in the subform (the main form = OrderDetails and the subform = SubDetails). My Combo box is named Department. If the user selects "Gift Certificate" from the combo box I want it to open the Gift Certificates Form. Since this is an order there may be other items on the Subform and Gift Certificate could be anywhere within the order. So how do I get it to automatically select [setfocus?] to the row where "Gift Certificate" has been selected?


'Opens the GC Form IF entered as an item / Department
If [Forms]![OrderDetails]![sbfOrderDetails].[Form]![Department] = "Gift Certificate" Then
DoCmd.OpenForm "Gift Certificates"
[Forms]![OrderDetails]![sbfOrderDetails].[Form]![Price] = Me!GCAmt
[Forms]![OrderDetails]![sbfOrderDetails].[Form]![OrderID] = Me!OrderID


End If

[June7]

Don't understand. If they just selected 'Gift Certificate' then the row has focus.

There is a table for gift certificates? Shouldn't it be related to OrderDetails and save the OrderDetails record ID as foreign key?

[Dal Jeanis]

Okay, you're trying to do all the work yourself, rather than letting Access do it.

Put that kind of code in the subform, in the combo box control's AfterUpdate event. If you want to be able to cancel the "department change" if they don't buy anything, you might do it in BeforeUpdate instead.

Private Sub cboDepartment_AfterUpdate()
  If cboDepartment = "Gift Certificate" Then
     DoCmd.OpenForm "Gift Certificates",,,,acDialog,txtKey
     ' any other stuff
     Me.Requery
  End If
End Sub

You can open the new form in dialog mode, as above, if you want to force the user to finish that form before proceeding with this one (highly recommended given your current workflow description.)

You can pass information to the popup form via the openargs argument, such as the example above that I assume you need to pass the key of the subform record (which is in a control named txtKey) to your "Gift Certificates". Here's a page that describes how to use OpenArgs. http://www.fmsinc.com/free/NewTips/A...ccesstip13.asp

If you pass the key of the subform rec, then the Gift Certificate form can directly update the underlying table with the price and orderid info, if necessary.

If you have more than one piece of information to pass down to the GC form, then you may be better off using [Tempvars] to pass information in both directions, and let the immediately following code in cboDepartment_AfterUpdate handle the update to the table. (where it says "any other stuff")

[nhdee]

Not quite... I don't want the gift certificate to open until after the user has taken payment. So I have my event procedure in the payment form (I have a "Close & Print" command button with on click event). Like I said, IF I manually highlight the Gift Certificate row it works perfectly with what I have. BUT I don't want the user to have to manually do anything. I DO want Access to realize that "Gift Certificate" is one of the items that has been choosen. All I need is for Access to select the gift certificate row? So I am not sure why you said I want to do all the work myself rather than let access do it???? Can't I setfocus to the gift certificate row? If so, how? All the things I have tried haven't worked.

[June7]

What event would let Access know that payment is received? Would this be a textbox entry or a button click?

Something like this might work.

With Me.RecordsetClone
.FindFirst "Department='Gift Certificate'"
If .NoMatch = False Then
Me.Bookmark = .Bookmark
End If
End With

[nhdee]

It is a button click to let Access know that payment is received. Would what you gave me go like this?: If so that doesn't work....

If [Forms]![OrderDetails]![sbfOrderDetails].[Form]![Department] = "Gift Certificate" Then
With Me.RecordsetClone
.FindFirst "Department='Gift Certificate'"
If .NoMatch = False Then
Me.Bookmark = .Bookmark
End If
End With
DoCmd.OpenForm "Gift Certificates"
[Forms]![OrderDetails]![sbfOrderDetails].[Form]![Price] = Me!GCAmt
[Forms]![OrderDetails]![sbfOrderDetails].[Form]![OrderID] = Me!OrderID
'Other stuff
End Sub

[June7]

Not quite. Remove the first End If, it is useless. It will only look at the current record in the subform. Move the OpenForm line to just after the Bookmark line.

Where is the button, on the main form or subform?

[nhdee]

Not quite. Remove the first End If, it is useless. It will only look at the current record in the subform. Move the OpenForm line to just after the Bookmark line.

Where is the button, on the main form or subform?

This doesn't work either but thanks for trying. Any ideas on just how to get the row with Gift Certificate to be selected by access?

[June7]

You didn't answer my last questions. If the button is on main form, which I think it is, then the suggested code must be modified to reference the subform container control. I always give subform container control a name different from the object it holds. If the form is named sbfOrderDetails, then I would name the container ctrDetails. I use this structure so I know it is possible.

With Me.ctrDetails.Form.RecordsetClone
.FindFirst "Department='Gift Certificate'"
If .NoMatch = False Then
Me.ctrDetails.Form.Bookmark = .Bookmark
DoCmd.OpenForm "Gift Certificates"
End If
End With

[nhdee]

The Main form = OrderDetails and the subform = SubDetails. The "Take Payment" button is on the main form which opens the payment form. After the payment is accepted the "Close & Print" button is clicked and that is when the Gift Certificate form can be displayed IF gift certificate was selected as an option from the combo box = Department on the subform (SubDetails).

When I change your code from ctrDetails to OrderDetails OR SubDetails I get Compile Error - Method or datamember not found. If I try [OrderDetails]![sbfOrderDetails] I don't get an error but the GC form comes up for all orders. My original posting everything works AS LONG AS I MANUALLY select the the row that has Gift Certificate.

[ItsMe]

Is form "Gift Certificates" a data entry form? Is the form's Data Entry property value = Yes?

[nhdee]

Is form "Gift Certificates" a data entry form? Is the form's Data Entry property value = Yes?

Yes it is a data entry form, the user can enter who the GC is to or from, the date, gc # and the amount I am pulling from the order.

[June7]

When you manually select the record, the GC form opens to a new record only?

Why would having focus on subform record cause GC form to be on a new Record?

Which table is the GC table related to?

Why do you need code to populate OrderID on the subform? Is OrderID the PK/FK? If so, the form/subform linking should automatically populate the subform field.

If you want to provide db for analysis, follow instructions at bottom of my post.

[nhdee]

Thanks! I can now get the Gift Certificates Form to open when there is a Gift Certificate sold on the Order Details SubForm (sbfOrderDetails). However, I can't get the price ("Price" on the order Details Subform) of that GC (GCAmt on the Gift Certificates Form) to automatically appear in the Gift Certificates Form. I tried putting:=[Forms]![OrderDetails]![sbfOrderDetails].[Form]![Price] as the default value for GCAmt on the Gift Certificate but that only works if I manually select "Gift Certificate" on the SubDetails.



'**************************GIFT CERTIFICATE FORM**************************************
Forms![OrderDetails]![sbfOrderDetails].SetFocus
With Forms![OrderDetails]![sbfOrderDetails].Form.RecordsetClone
.FindFirst "Department='Gift Certificate'"
If .NoMatch = False Then
Me.Form.Bookmark = .Bookmark
DoCmd.OpenForm "Gift Certificates"
End If
End With

[ItsMe]

Not sure why you would need to, but you can place your code in an unbound textbox. You can click inside the textbox and replace "Unbound" with

=[Forms]![OrderDetails]![sbfOrderDetails].[Form]![Price]