[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Setting a default value for a cbo in VBA

Posted on 2009-04-21
6
Medium Priority
?
399 Views
Last Modified: 2013-11-28
Hey All,
I have the following procedure designed to place values in a drop down list on a form.
This part work well but I would like to code in a default value "09X- " like
Me.cbo_Year.AddItem Format(Now, "yy") & " X-" but I'm not sure how to write it or where to put it.

In the code box you can see a bigger picture of the code snipit.
Can anyone help me out?

    Me.cbo_Year.AddItem Format(Now, "yy") & " X-"
    Me.cbo_Year.AddItem Format(Now, "yy") & " U-"
   'Me.cbo_Year.AddItem Format(Now, "yy") & " P-" JC 10-Mar-09
    Me.cbo_Year.AddItem Format(Now, "yy") & " O-"
 


Me.cbo_Year.AddItem Format(Now, "yy") & " X-"
Private Sub Form_Open(Cancel As Integer)
On Error GoTo OpenForm_Error
    q_CurUser = UCase(CurrentUser())
    f_OpenArg = UCase(Forms![frmH_AddSoln].OpenArgs)
   
    Me.cbo_Year.AddItem Format(Now, "yy") & " X-"
    Me.cbo_Year.AddItem Format(Now, "yy") & " U-"
   'Me.cbo_Year.AddItem Format(Now, "yy") & " P-" JC 10-Mar-09
    Me.cbo_Year.AddItem Format(Now, "yy") & " O-"
    
   If (q_CurUser = k_ADMIN) Or (q_CurUser = k_MGR) Or (q_CurUser = Admin) Then
      cbo_Status.Visible = True
      cbo_Status.Enabled = True
      lbl_Status.Visible = True
      cbo_Year.Visible = True
      cbo_Year.Enabled = True
      Lbl_SolnPre.Visible = True
     
   Else
      cbo_Status.Visible = True
      cbo_Status.Enabled = False
      lbl_Status.Visible = True
      cbo_Year.Visible = True
      cbo_Year.Enabled = False
      Lbl_SolnPre.Visible = True
         
   End If

Open in new window

0
Comment
Question by:jcuzzola
  • 3
  • 3
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24194682
By setting the combo value to the value you want, that should find it for you

me.mycombo.value = "somevalue"
0
 

Author Comment

by:jcuzzola
ID: 24194715
Where does this statement go? Will the drop down list still be created?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 24194843
after u create the dropdown with populated data, and when you are ready to use it, do it then

so taking your example

    Me.cbo_Year.AddItem Format(Now, "yy") & " X-"
    Me.cbo_Year.AddItem Format(Now, "yy") & " U-"
   'Me.cbo_Year.AddItem Format(Now, "yy") & " P-" JC 10-Mar-09
    Me.cbo_Year.AddItem Format(Now, "yy") & " O-"

If u did

Me.cbo_Year.value = "U-"

then you will find that value appearing in the list first

Is this what you mean by default value?

Or do you mean default value when nothin selected

If you find cbo_Year.value is null then you can assign default value then

if isnull(cbo_Year.value) then
    'its null, use default value

or possibly use NZ

NZ(cbo_year.value,"U-")

if cbo_year is not null then use whatever value is selected, if none selected then use U-
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:jcuzzola
ID: 24194988
I'm trying to place the default value of "Format(Now, "yy") & " X-" which would be = 09X-... to a user
with limited access. The else staement below desables  cbo_Year.Enabled = False so the user with limited access will have the field cbo_Year populated with the current year and X. 09X, 10X, 11X...

Running the code below gives an error like can't set value for the field.

Any thoughts?

Else
      cbo_Status.Visible = True
      cbo_Status.Enabled = False
      lbl_Status.Visible = True
      cbo_Year.Visible = True
      cbo_Year.Enabled = False
      Lbl_SolnPre.Visible = True

     ' Me.cbo_Year.Value = "U-"
      'Me.cbo_Year.Value = Format(Now, "yy") & " X-"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24195234
So as long as the value you are trying to set exists in there, it should be okay. Now I just tried it and didnt get a failure. This error, was there an error number displayed also?

Is this default value you wanting to set always the first item in the list?


0
 

Author Comment

by:jcuzzola
ID: 24213925
Rocki,

Sorry, I got side tracked. The solution you provided worked well.

Joe
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question