Change text box format based on combo box selection (1 Viewer)

[Sticky99]

Hi Guys,
Is it possible to format a text box based on the selection in a combo box. What I need is when “Date” is selected in the combo box the text box needs to be formatted as a date field.
Any help would be great.

Thanks

 

[isladogs]

Can you explain the context as there's probably a better solution

 

[ontopofmalvern]

Have an "on change" event. something llike this

If me.cmbx = "Date" Then
Me!txtBox.BorderColor = Red
Else if Me!.cmbx = "Name" Then
Me!txtBox.BorderColor = Blue
Else if....

If the there are lots of variables you maybe better off with a' Select case' type thing.


These two links might help

TextBox object (Access)

Office VBA reference topic

docs.microsoft.com

Select Case statement (VBA)

Office VBA reference topic

docs.microsoft.com

Hope this helps
Richard

 

[isladogs]

Richard
Whilst you can easily change border colour and other properties, how does that address the question about formatting the text box according to the datatype selected in the combo?

 

[Sticky99]

Can you explain the context as there's probably a better solution

Hi Isladogs,
The text box is used in a search query, I have 5 options in the combo box and the search results are returned on the text value in the text box. One of the options is “Date” so I would like to give the option of selecting the date from a date picker.

 

[isladogs]

What are the other 4 combo options

 

[Sticky99]

Part No., Description, Supplied By & Serial No.

 

[CJ_London]

perhaps something like this in the combo after update event

if combo="Date" then textbox.format="short date" else textbox.format=""

 

[Sticky99]

perhaps something like this in the combo after update event

if combo="Date" then textbox.format="short date" else textbox.format=""

Thnank CJ_London, I'll give it a try and let you know.

 

[Micron]

I don't see how a combo with all those different value types could ever contain a date. Any table field providing all those different "types" would have to be text and I suspect that value list members can only be text, therefore there can be no date data type here, and if the "dates" are dynamic you can't refer to them explicitly. What you could do is validate that the selected item is a date...
If IsDate(combo) Then

 

[Sticky99]

Thnank CJ_London, I'll give it a try and let you know.

Hi CJ_London, I get an error "The IF and Else If conditions are required arguments". Any ideas?

 

[isladogs]

Part No., Description, Supplied By & Serial No.

I actually meant what datatype is used for the other 4 options?

 

[CJ_London]

get an error "The IF and Else If conditions are required arguments". Any ideas?

not without knowing what you are actually using

 

[Sticky99]

Thanks for your comments guys, much appreciated.

 

[moke123]

so I would like to give the option of selecting the date from a date picker.

If you look on isladogs website he has an excellent sample datepicker. You could always add code that if the combobox = date, it would then open the datepicker or make it visible if embedded in the form.

 

[Pat Hartman]

I would fix the design flaw in the table. Tables are not spreadsheets. EVERY row for a column should contain the same data type. So if sometimes you have a date and sometimes you have a string, you should use two columns. The only way the table will even allow this mish-mash is if you define the data type as text which of course is what is causing your formatting problem.

 

[moke123]

I think we need clarification from the OP. They way I'm reading his/her question is that s\he has an unbound textbox which supplies a variable to a query and wants a datepicker should date be chosen in the combobox for ease of data entry.

 

[jdraw]

Agree that more context/examples needed. Seems a bit of mishmash that could very well be attributed to table design, business process(es) involved.
Date, Part No., Description, Supplied By & Serial No. seem separate, but related, and may indicate "which process" to do next????? more info please.

 

[MajP]

I tested this and it works as CJ_London describes. I am not sure of the confusion, because the request seemed pretty logical to me. You have a combo box that lists fields to filter and a textbox to enter the search value. If you change the format to a date format the date picker will be available, change it back and it goes away. I would use a custom date picker for many reasons, but this does work as the OP asked.

Private Sub cmboField_AfterUpdate()
   If Not IsNull(Me.cmboField) Then
     If Me.cmboField = "Date Hired" Then
     Me.txtSearch.Format = "Short Date"
  Else
    Me.txtSearch.Format = ""
     End If
   End If
End Sub

Private Sub CmdSearch_Click()
Dim fltr As String
If Not IsNull(Me.cmboField) And Not IsNull(Me.txtSearch) Then
Select Case Me.cmboField
Case "Date Hired"
fltr = "[DateHired] = #" & Me.txtSearch & "#"
Case "Company"
fltr = "[Company] = '" & Me.txtSearch & "'"
Case "First Name"
fltr = "[First Name] = '" & Me.txtSearch & "'"
End Select
Me.Filter = fltr
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
Me.Refresh
End Sub

Private Sub CmdClear_Click()
  Me.Filter = ""
  Me.FilterOn = False
End Sub

 

[Sticky99]

I tested this and it works as CJ_London describes. I am not sure of the confusion, because the request seemed pretty logical to me. You have a combo box that lists fields to filter and a textbox to enter the search value. If you change the format to a date format the date picker will be available, change it back and it goes away. I would use a custom date picker for many reasons, but this does work as the OP asked.

Private Sub cmboField_AfterUpdate()
   If Not IsNull(Me.cmboField) Then
     If Me.cmboField = "Date Hired" Then
     Me.txtSearch.Format = "Short Date"
  Else
    Me.txtSearch.Format = ""
     End If
   End If
End Sub

Private Sub CmdSearch_Click()
Dim fltr As String
If Not IsNull(Me.cmboField) And Not IsNull(Me.txtSearch) Then
Select Case Me.cmboField
Case "Date Hired"
fltr = "[DateHired] = #" & Me.txtSearch & "#"
Case "Company"
fltr = "[Company] = '" & Me.txtSearch & "'"
Case "First Name"
fltr = "[First Name] = '" & Me.txtSearch & "'"
End Select
Me.Filter = fltr
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
Me.Refresh
End Sub

Private Sub CmdClear_Click()
  Me.Filter = ""
  Me.FilterOn = False
End Sub

Thanks MajP, works perfectly!

Thank you