Combo Box on Form

[martingaleh]

I have an combo box on a form. The form is backed (or best verb here) by a recordset. I want the combobox to either select a record from the recordset that the form is bound to or make a new record and bind the form to that record based on whether the combo box value can be found in a specified field in the recordset of the form. These combo boxes were really designed to help fill data from a child table to a master table or query through a foreign key, it seems, so all the automation on access is geared toward that. So I coded this into the onupdate event of my unbounded combo box:

Private Sub Combo14_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "Name = """ & Me![Combo14] & """" <-Really?  Is there no better way to pass a parameter into a string in the year 2016
    If rs.NoMatch = False Then <- Unbelievably, EOF doesn't work because if it doesn't find at all, the computer's bookmark is still on the first file, not at the end of the file. 
        Me.Bookmark = rs.Bookmark
    Else
        DoCmd.GoToRecord , , acNewRec <-Make a new record, because the new name I put into the combo box can't be found.  Can I use rs.addNew and update here?  You would think but Microsoft is not bound by logic and rationality
        Me!Name = Combo14.Value <-here, the new recordset is bound to the form and the I can access the underlying fields in the record directly
    
        If Me.Dirty Then <-or I could requery, but why
            Me.Dirty = False <-is it possible true, that when I requery, the form reverts to the first record and I have to do the find again?
        End If
    End If
    rs.FindFirst "Name = """ & Me![Combo14] & """" <-I have to find again 
    Me.Bookmark = rs.Bookmark
    Me.Requery
    Me![Combo14].Requery
End Sub

Is there a much smarter way to code this, because this is all forms of retarded. I'm querying twice down an unordered list. I can't get references to records I've set. Most major programming platforms give you a handle to refer to what you're making, this is like the first one that doesn't. I must be doing things so against the grain that I'm hitting all these problems from the last century. Please help.

[ranman256]

instead of all that code, can you just

Private Sub Combo14_AfterUpdate()
  if IsNull(Combo14) then
   me.filterOn = false
 else
   me.filter= "[Name] = '" & Combo14 & "'"
   me.filterOn = true
end sub

[martingaleh]

Yes, this is if the combo box is bound to the form and I want to find an existing record in the recordset. What if I want to add a new record? I set the combobox to unbounded with underlying recordset the same as the recordset of the form with just the fields I need for the combo box and when the value in the combo box can't be found on the field it's bound to I need to:
1. Make a new record
2. Set the form to the new record
3. requery the subform that is bound to the main form by its foreign key
4. requery the combo box to include that new record I just made

Because it seems access doesnt' do any of this automatically

[ssanfu]

"Name" is an extremely poor name for a field. It a reserved word and is non-descriptive - "Name" of what? Your name? First Name? The name of a car you own? The city you were born in?

I would use

rs.FindFirst "Name = "'" & Me![Combo14] & "'"

I don't know your process, so if you use a combo box the select a name, it should be found.
Are you typing a name into the combo box?

Have you investigated the combo box "Not In List" event?

[martingaleh]

Name was temp code. We don't actually use it for production, it was just to demonstrate the code. I figured it out. Actually, my solution is really terrible, but I think it's how ms access works. Again, there is a combo box on the form to select the record or make a new record if the input value is not in the combo box.

1. This requires code and here is the code to return an id number:

Set rsQB = CurrentDb.OpenRecordset("public_bundles", dbOpenDynaset)


    With rsQB
                
            'Find first does not work with a table recordset, which is quite unbelievable.  Also, combo boxes don't always have columns
            .FindFirst "Name = """ + Name + """"
            'By the by, if findfirst doesnt' find any, .eof is still false!  The cursor stays on the first record.
            If .NoMatch = True Then
                'We're in luck, this code has already been made
                .AddNew
                ![Name] = Name
                'and then you have to update it.  Sad but true
                .Update
                .Requery
                .MoveFirst
                'bookmark last modified doesn't work with postgre the way it works with sharepoint
                .FindFirst "Name = """ + Name + """"
                getIDFromName = .bundlesID
            Else
                getIDFromName = .bundlesID
            End If
        .Close
        End With

The critical key here is if you need to make a new record, do not use .bookmark = .lastmodified nonsense because it will say the record has been deleted, probably because it's postgre. The right thing to do if you made a new record is to search for it. It takes more time, but that's how access was put together.

2. This kinda works

set clone = Me.recordsourceclone
with clone
.findfirst "Name = " + name <-with all the quotes bullcrap
form.bookmark = .bookmark

3. If you have a subreport, setting the parent and child fields to filter the subreport with the value from the parent does not work. You have to physically manipulate the recordsource of the child off of an event like this:

 Me![Child16].Form.RecordSource = "you're favorite query"
        Me![Child16].Form.Requery

4. This is the hardest part. The data underlying the combo box will be updated with the new value, but then the combo box will reset. So before doing step 3 above, its' best to properly setup the combo box

Me![Combo14].Requery
    Me![Combo14].Value = Name

So do step 3 before 4.