Update dropdown via an input field?

[bizchris2001]

Can I update a dropdown using an input field?

Hi all,

I'm trying to make it so I can update a dropdown list via an input field. For example, the dropdown pulls from the Type field, and currently has: Rose, Tulip, Lily. I want the user to be able to choose Tulip from the dropdown, type "Chrysanthemum" in the input field, and when they click Submit, voila! The dropdown is now Rose, Chrysanthemum, Lily.

So far, I've got the dropdown (Combo0) working as such:
SELECT [tbl_Flowers].[PlantID], [tbl_Flowers].[CommonName] FROM tbl_Flowers;

No big deal, right? Now, the input field (simply named Text2) has no event behind it, and the Submit button (cmd_Update) has the following:

Private Sub cmd_Update_Click()
On Error GoTo Err_cmd_Update_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmd_Update_Click:
Exit Sub

Err_cmd_Update_Click:
MsgBox Err.Description
Resume Exit_cmd_Update_Click

End Sub


Thoughts on how I could make the input field on the form update the dropdown values? Thanks! Chris

 

[WayneRyan]

Chris,

Private Sub cmd_Update_Click() 
DoCmd.RunSQL "Update tbl_Flowers " & _
             "Set    CommonName = '" & Me.Text2 & "' " & _
             "Where  CommonName = '" & Me.Combo0.Column(1) & "';"
Me.Combo0.Requery
End Sub

That will do it, but I don't think that that is what you really
want to do.

Wayne

 

[bizchris2001]

Actually Wayne, that's exactly what I wanted to do!
One thing, though: when I click the button it gives me the standard "Warning: You're about to update 1 record" message - how can I turn that off? Thanks, Chris

 

[WayneRyan]

Chris,

You can go to Tools --> Options --> Edit/Find and there is a
confirm option that you can uncheck. This will do it for ALL.

Or you can do:

DoCmd.SetWarnings False
... Your code ...
DoCmd.SetWarnings True

Back up before doing this! (I mean the update, not the warnings)

Wayne

 

[bizchris2001]

Wayne, doesn't this do it for just my machine though? I.e., if I have the Access db on a network drive, users whose default is not set that way would still get it? (which may mean I would use the code instead, but want to verify) Thanks, Chris

 

[SforSoftware]

Yes, it's a machine-setting.

If you want, you can set this machine-setting also by code, using SetOption. For more information, see this link:
http://msdn.microsoft.com/library/d...0/html/achowSettingOptionsFromVisualBasic.asp

 

[WayneRyan]

Chris,

Use the code ...

Wayne

 

[bizchris2001]

Fabulous, this did the trick - thanks!