Combo Box as a parameter to VBA

S

SAm

Hi

I am a bit new to VBA. i have a sub that is tested and works fine. i tried
now using in another form and i came accross an interesting issue. i have a
combo field with a list of ten values (text), which is used in dcount as
criteria. the last value of the combo box is the only one that vb didn't
recognize. anybody have some clues as to why this is happening, and how to
circumvent this issue?

thanks,

sam
 
S

SAm

hi ken,

i will copy the relevant code:

relevant assignements:
strFacility = Me!FacilityName
dteStart = Me!date

there is a while loop here,
If DCount("*", "RecordHours", "RecordDate = #" & dteCurrent & "#" & _
" And FacilityName = '" & strFacility & "'") = 0 Then
lstDate.AddItem dteCurrent & ";" & strFacility
End If

the Me!FacilityName is a combo box with a few text values. if the last one
doesn't end with a semi colon, then the last one is not recognized. if the
last one ends with a semi colon then it is recognized by the dcount, but it
adds a null value to the combo box.

these are the values in the combo box:
Sho;Ter;Woo

thanks,

sam
 
K

Ken Snell [MVP]

What does the DCount function have to do with the FacilityName combo box?
The DCount function is reading the "RecordHours" table or query. What is
lstDate?

Post the entire procedure's code... I still do not understand what you're
trying to do here.

--

Ken Snell
<MS ACCESS MVP>
 
S

SAm

Private Sub Command3_Click()
'On Error GoTo Err_Command3_AfterUpdate

Dim dteStart As Date, dteCurrent As Date, strFacility As String

' Now starts the date missing listing

strFacility = Me!FacilityName
dteStart = Me!date

' Set the date to search for to the Start date
dteCurrent = dteStart
' Clear the listbox which contains the missing dates
lstDate.RowSource = ""

' As long as the current month is the same as the start month, keep
checking
Do While Year(dteCurrent) >= Year(dteStart) - 1 And Year(dteCurrent) <=
Year(dteStart)
' If the are no records in the table with this date, add it to the
list
If DCount("*", "RecordHours", "RecordDate = #" & dteCurrent & "#" & _
" And FacilityName = '" & strFacility & "'") = 0 Then
lstDate.AddItem dteCurrent & ";" & strFacility
End If
' Get the next date to check
dteCurrent = DateAdd("d", -1, dteCurrent)
Loop
MsgBox "No more Records", vbOKOnly
Exit_Command3_AfterUpdate:
Exit Sub

Err_Command3_AfterUpdate:
MsgBox Err.Description
Resume Exit_Command3_AfterUpdate

End Sub


basically, what this does is look at a table and tries to find missing
records for calendar days. each on the calendar should have an entry. if
doesn't have it, the day gets added to the list.

thanks,

sam
 
K

Ken Snell [MVP]

I am sorry, but I still do not understand what the problem is here.

The code seems straightforward. I do note that there is a comment about
testing the month when in fact the code is testing for the year -- perhaps
that logic error is what is the source of your problem?

Other than that, I see nothing in this code that should be a problem with
what is in the FacilityName combo box's list of values.

--

Ken Snell
<MS ACCESS MVP>
 
S

SAm

thanks thanks,

i wasn't aware of the comment error, will update. was due to the copy/paste
and then i modified the year without correcting the comment.

to reiterate, the issue is that the combo has a list with values and the
last value in the list is not recognized unless i add a semicolon.

again, thanks alot, if anybody knows a solution plz let me know

sam
 
K

Ken Snell [MVP]

Any chance that the last item in the combo box's list has a space at the end
of it?
 
S

SAm

nopes, no spaces, i even tried to add a trim to the assignment and then later
to the if statment, and noting. doesn't come through. and when i add a
semicolon it works.

weird!

sam
 
K

Ken Snell [MVP]

Without the semicolon, does the combo box correctly show the three items
that you listed: Sho;Ter;Woo

You're saying that, if you select Woo in the combo box, the DCount function
is not using the string "Woo" in its count of the records in the table? But
if you have a semicolon in the combo box list: Sho;Ter;Woo;

then the DCount function works as you expect?

Put a breakpoint on the DCount step. Then use your form to select the Woo
value, and then run the code. When the code breaks at the breakpoint, put
your cursor over the strFacility variable in that step -- what does the
"popup" box show is its value?
--

Ken Snell
<MS ACCESS MVP>
 
S

SAm

just tested it, and you are 100% on the money. the value has a trailing
space. but why??? and how do i get rid of it.

i already added two trim statements,
1. strFacility = Trim(Me!FacilityName)
2. If DCount("*", "RecordHours", "RecordDate = #" & dteCurrent & "#" & _
" And FacilityName = '" & Trim(strFacility) & "'") = 0
Then
and it seems that it didn't help, what can i do?

sam
 
K

Ken Snell [MVP]

I'm guessing that it's not a trailing space then, but instead is a carriage
return or a line feed character, or both.

When the code breaks, type the following into the intermediate window and
let's see what you get:

?InStr(strFacility, Chr(13))

?InStr(strFacility, Chr(10))


What numbers are returned by the two function tests?
--

Ken Snell
<MS ACCESS MVP>
 
S

SAm

i have no clue what these numbers mean. (i wouldn't mind if you explain).

but these are the results:

?InStr(strFacility, Chr(13))
4
?InStr(strFacility, Chr(10))
5
 
S

SAm

ok, i looked it up. i understand now. i have both a carriage return and a
line feed. how do i get rid of it? and how did it end up there in the first
place?

thanks,

sam
 
K

Ken Snell [MVP]

Easiest way is to open the form in design view, click in the Row Source for
the combo box, put the cursor just after the last entry's second o, then
press the Delete key at least two times.

If your combo box is getting a row source list from somewhere else, you'll
need to go to the source data and do what I describe above. The carriage
return and line feed characters are right after that second o.
 
S

SAm

i feel so stupid. i can't believe it, it worked. i am sure you can imagine
that i familiar with hidden charachters, but this has really tricked me.

thanks a million,

sam
 
K

Ken Snell [MVP]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

SAm said:
i feel so stupid. i can't believe it, it worked. i am sure you can imagine
that i familiar with hidden charachters, but this has really tricked me.

thanks a million,

sam
Click to expand...
 
Top