Update form after combo box change? 2

[Asspin]

Ok, it's been a while since I messed with Access, but what I am trying to do is update a form to pull a record up, after a dropdown is changed. I have 3 fields on my form, Name, Supervisor, Skills. There is also a subform with detailed data. I changed the name box to a combo box and it seems the subform is updating. I need the other 2 boxes on the form to update as well. I tried the following code, but obviously I am way off! Any help would be great!

Private Sub sName_AfterUpdate()
    Me!sSupervisor.Requery
    Me!sSkill.Requery
End Sub

Dan

 

[Asspin]

Oh and here is what I am using for my combo boxes source...

SELECT tMain.sName FROM tMain ORDER BY tMain.sName;

Dan

 

[TheAceMan1]

How are ya Asspin . . .

If the mainform and subform were [blue]bound[/blue], and the subform was [blue]linked[/blue] to the mainform with [blue]Master/Child Link[/blue] properties, all you'd need in the combobox [blue]After Update[/blue] to perform a lookup would be:

[blue]   Me.Recordset.findfirst "[sName] ='" & Replace(Me!][purple][B][I]ComboboxName[/I][/B][/purple]],"'","''") & "'"[/blue]

For the above to work you'll have to make the combobox [blue]unbound[/blue] and move it to the header or footer section. Once the combobox is moved you need to restore the [blue]sName[/blue] textbox.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Asspin]

That was just what I needed. Spot on!

Dan

 

[Asspin]

One more question. Selecting the user from the combo box is now working, the other thing I would like to get it to do, is allow me to type a name into the combo box to create a new record. Any idea here?

Dan

 

[PHV]

Have a look to the NotInList event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Asspin]

Ok, I pulled up the help for that, I am guessing I need something like this? I don't even get the message box popup when I try to add a new user.

Private Sub sName_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
    ctl = Me!sName
    If MsgBox("User is not in list. Add them?", vbOKCancel) = vbOK Then
        Me!sSupervisor.Text = ""
        Me!sTopSkill.Text = ""
        Response = acDataErrAdded
        ctl.RowSource = ctl.RowSource & ";" & NewData
        DoCmd.Save
        Me!sName.Requery
    Else
        Response = acDataErrContinue
        ctl.Undo
    End If
End Sub

Dan

 

[PHV]

The VBA help for the NotInList event clearly states that the LimitToList property should be set to True.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Asspin]

So it does! My bad! Once I have this set though, I have errors in my code.

ctl = Me!sName
Gets Object variable or With block variable not set

Me!sSupervisor.Text = ""
You can't reference a property of meathod for a control unless the control has the focus.

What am I doing wrong here?

Dan

 

[PHV]

[!]Set [/!]ctl = Me!sName
Me!sSupervisor.[!]Value[/!] = ""
Me!sTopSkill.[!]Value[/!] = ""

Anyway, if the RowSourceType property of sName is a Query/Table then I don't think you can add the NewData like this.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Asspin]

Hmm, you seem to be right, it doesn't look like this is adding to the table. Is their any other way to do this?

Also I am getting "The Object Type argument for the action or method is blank or invalid" for DoCmd.Save acDefault

Here is my current code...

Private Sub sName_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
    Set ctl = Me!sName
    If MsgBox("User is not in list. Add them?", vbOKCancel) = vbOK Then
        Me!sSupervisor.Value = ""
        Me!sTopSkill.Value = ""
        Response = acDataErrAdded
        ctl.RowSource = ctl.RowSource & ";" & NewData
        DoCmd.Save acDefault
        Me!sName.Requery
    Else
        Response = acDataErrContinue
        ctl.Undo
    End If
End Sub

Dan

 

[PHV]

I'd use a modal bound form to adding NewData and related infos into the table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886