Help plz, Can't get combo box to find record/populate (1 Viewer)

[morfusaf]

I have an Update event form...

Oh this form, I want to have a "what date was event ComboBox"... then have a "Which event on that day?" Combobox... (I have this part working).... I have the cascading box working.

What I need is, once I select the event on the second box, i need it to update the entire form and populate with which record I selected.

I know I need a afterUpdate event... but I don't know how to code it.

I have tried everything i can think of and it isn't working.

Form is = formUpdateEvent
2 cascading boxes = btnDate btnEvent(I need event to afterupdate)
table Im pulling from is = tblEvents
and I pretty much want to populate any box/checkmark that is on this form.

 

[sparks80]

Hi,

You can use the findfirst method of a DAO recordset to find the record you want like this:

Private Sub YourComboboxName_AfterUpdate()
    Dim rs As DAO.Recordset

    Set rs = YourFormName.RecordsetClone

    rs.Findfirst ("YourDateFieldName=#" & YourDateComboBoxName.Value & _
        "# AND YourEventFieldName='" & YourEventComboBoxName.Value & "'")
    If rs.NoMatch Then
        MsgBox "Sorry, no relevant events were found"
    Else
        YourFormName.Bookmark = rs.Bookmark
    End If

    Set rs = Nothing
    DoCmd.GoToControl "YourComboboxName"
End Sub

 

[morfusaf]

Well I copied it and its not working,

I get an error
"
Run-time Error '424:

Object Required "

When I hit the debugger it says something is wrong with
Set rs = formUpdateEvent.RecordsetClone

Here is my code...

Private Sub cboEvent_AfterUpdate()
 
    Dim rs As DAO.Recordset
 
    Set rs = formUpdateEvent.RecordsetClone
 
    rs.FindFirst ("EventDate=#" & cbodate.Value & _
        "# AND DetailEventTitle='" & cboevent.Value & "'")
    If rs.NoMatch Then
        MsgBox "Sorry, no relevant events were found"
    Else
        formUpdateEvent.Bookmark = rs.Bookmark
    End If
 
    Set rs = Nothing
    DoCmd.GoToControl "cboEvent"  
 
End Sub

 

[boblarson]

Okay, first off I am going to say something which may confuse you but it is important.

What data are you wanting to fill from selecting the second combo box and are you trying to store it? If the selection of the item from the second combo box populates other fields in order to store it, I would say your architecture is incorrect. You should not store redundant data. However, if it is just DISPLAYING the extra data, then that is quite fine and can be accomplished simply by including that information in the combo box's row source and then setting the column count and column widths appropriately so then you can refer to the combo from the text boxes by using something like this:

=[ComboNameHere].[Column](4)

(4 in this case represents the FIFTH field in the row source as it is ZERO BASED)

 

[boblarson]

Okay, I may have misread the post. If you are just navigating to the record you want, then that is just a matter of using what Sparks80 posted.

The big question if you are getting the error from the find first code, is what value are the combo boxes returning? You need to determine that because it may be returning something which is not what you want. Many times, if you set up the combo with the Wizard, it will still put a Key Field in with it and not just the single field you wanted.

 

[morfusaf]

Basically,

I have one form to AddEvent , ... in this form, I add title, date, location, and abunch more information (this is stored in the tblEvents).

and the second form(one I need help with) is called UpdateEvent What I need this one to do, is Save record... not create new one.

So, i want to recall a record, from my selected combo box... then that should populate all the fields on the form according to what that record is. After that I have a button at the end for Save Record.

Make sense?

 

[boblarson]

Make sense?

Yes, and what Sparks80 provided should work AS LONG as your combo boxes are returning the correct things. So, again, I would check to see what their values are and you can do that by setting a breakpoint in your code and then hovering your mouse over the code referring to them or you can go to the VBA IMMEDIATE WINDOW and type in

?ComboNameHere

and hit enter and it will show you the value.

 

[morfusaf]

I tried the ?cboEvent in the vbaimmediate window but nothing happens?

 

[morfusaf]

Ok,


To get the cascading boxes to work, i used the query builder... it was the only way I could figure it out...

the code for my second combo box Row Source is...

SELECT tblEvents.DetailDate, tblEvents.DetailEventTitle FROM tblEvents WHERE (((tblEvents.DetailDate)=[forms]![formUpdateEvent].[cboDate])) ORDER BY tblEvents.DetailEventTitle;

I followed a tutorial to get that to work, becuase i couldn't get anyone to help out writing code for my boxes.

I have no idea what that does.

 

[boblarson]

Ok,


To get the cascading boxes to work, i used the query builder... it was the only way I could figure it out...

the code for my second combo box Row Source is...

SELECT tblEvents.DetailDate, tblEvents.DetailEventTitle FROM tblEvents WHERE (((tblEvents.DetailDate)=[forms]![formUpdateEvent].[cboDate])) ORDER BY tblEvents.DetailEventTitle;

I followed a tutorial to get that to work, becuase i couldn't get anyone to help out writing code for my boxes.

I have no idea what that does.

Okay, so based on that, your second combo's value (if you have left COLUMN 1 as the Bound Column property) it would be returning the date instead of the event title what you want. Change the Bound Column property of that combo box to 2 from 1 and it should work.

EDIT: And make sure the COLUMN COUNT of that combo is set to 2 and not 1.

 

[morfusaf]

Exact same error.

Event with count...

 

[morfusaf]

my DB is attached in zip..


(first 2 events are just test events..

I am working on the formUpdateEvent

 

[boblarson]

I am so sorry. It just dawned on me what is happening. You said it errored with this:

When I hit the debugger it says something is wrong with
Set rs = formUpdateEvent.RecordsetClone

You need to use the correct code to refer to the subform. You need to refer to the subform control (control that houses the subform on the parent, but is not the subform) and use the .Form. part as well. So, if your subform is named formUpdateEvent but the control that houses it on the main form is named Child7 you need to use

Set rs = Me.Child7.Form.RecordsetClone

or if they are the same exact name as each other (subform control and subform)

Set rs = Me.formUpdateEvent.Form.RecordsetClone

(the .Form. part leave in there and don't change it. Use it just as I showed).

 

[boblarson]

my DB is attached in zip..


(first 2 events are just test events..

I am working on the formUpdateEvent

I only have 2003 here at work so can't open an Accdb file. Also, the event you should be working with is the AFTER UPDATE event of the second combo box.

 

[morfusaf]

Save in 2003 compatable.

 

[sparks80]

Sorry, that is partially my fault - I didn't realise that the recordset that needed updating was on a subform.

I'll take a look and get back to you

 

[morfusaf]

Ok, subform form...

I have a form, (which is where the combo boxes are, as well as MOST of the fields i want populated).

I use a junction table, to link stuff together so I have a Subform that adds Who went on this event...

 

[boblarson]

Okay, here's the corrected code. I think we were all not on the right page as you really wanted to find the record in the main form and then the subform would just come along for the ride.

Private Sub cboEvent_AfterUpdate()
    Dim rs As DAO.Recordset
 
    Set rs = Me.RecordsetClone
    rs.FindFirst ("DetailDate=#" & cbodate.Value & _
        "# AND DetailEventTitle='" & cboevent.Value & "'")
    If rs.NoMatch Then
        MsgBox "Sorry, no relevant events were found"
    Else
        Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
    DoCmd.GoToControl "cboEvent"
 
End Sub

PLEASE NOTE: Your bound column property on the second combo was set to 3 (it NEEDS to be 2).

 

[morfusaf]

OMG I LOVE YOU SO MUCH... I have been working on this exact same combo box for 2 days!

 

[morfusaf]

I Changed it to 3, just to see what the heck that does.. not really sure how that bound column works ...