Combo Box Problem

B

Bob Vance

My Combo Box has no Control source and the Row Source type is Value List
My Row Source is showing say 18;Dobbie,Chris
Bound Column 1
My format is 3 column count 0cm:7cm;0cm but even if i try to show the last
Column I am not getting "Chris" only when I click the arror do I see the
name
At the moment when I open my form I am getting just Dobbie and would like to
get Dobbie, Chris
 
J

Jeff Boyce

Bob

It sounds like you are expecting the combobox to display more than one
column AFTER you've made your selection. That isn't how it works.

If you want to see column 2 and column 3 after selection, you have a couple
possibilities...

One is to do away with column 3! Instead, use something like 18; "Dobbie,
Chris".

Or another would be to add a text box into which you have Access 'shove'
that third column after selection.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
B

Bob Vance

Thanks Jeff, my combo box cbOwnerName is being controlled by this!
tblInvoice.OwnerName is correct Dobbie, Chris
Regards Bob

If CurrentProject.AllForms("frmMain").IsLoaded = True Then
cbOwnerName.RowSource = Form_frmMain.cboClient & ";" &
Form_frmMain.cboClient.Column(1)
cbOwnerName.value = Forms!frmMain!cboClient.Column(1)
recInvoice.Open "SELECT * FROM tblInvoice where OwnerName='" &
cbOwnerName.value & "';", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recInvoice.BOF = False And recInvoice.EOF = False Then
If recInvoice.Fields("OwnerAddress") = "" Or
IsNull(recInvoice.Fields("OwnerAddress")) Then
recInvoice.Fields("OwnerAddress") = ""
Else
tbAddress.value = recInvoice.Fields("OwnerAddress")
End If
End If

cmdAdd_Click

Else
recInvoice.Open "SELECT * FROM tblInvoice where InvoiceID=" &
Forms!frmMain!frmModifyInvoiceClientFromMain.Form!lstModify.value _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
subShowInvoiceValues
subShowInvoiceDetailValues

If CurrentProject.AllForms("frmMain").IsLoaded = True Then
cbOwnerName.value = Form_frmMain.cboClient.value
Else
cbOwnerName.value = recInvoice.Fields("OwnerID")
End If
 
J

Jeff Boyce

I don't see a question in this...

I don't understand what you mean by "my combobox is being controlled by
this... I thought you said the combobox was 'fed' by a value list...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
B

Bob Vance

Thanks Jeff it does have a Value List, but what ever way I change it I can
not get Dobbie, Chris Only Dobbie
So I assumed it must be a code controlling the Combo Box!
It would have nothing to do with the case that opens the Form would it?
DoCmd.OpenForm "frmInvoiceClient", , , , , , "ModifyOldInvoice"
Regards Bob Vance
 
B

Bob Vance

Should I try and force a textbox from a query with the same ClientID Number
to give the name...Thanks Bob
 
S

Steve

Hi Bob,

I think the only way you can get Dobbie, Chris is to change the Row Source
Type to a query with two fields. The first field returns 18. The second
field is a calculated field that returns Dobbie, Chris. You will have to
change Column Count to 2 and Column Width to 0;7 cm.

Steve
(e-mail address removed)
 
J

John W. Vinson

Thanks Jeff it does have a Value List, but what ever way I change it I can
not get Dobbie, Chris Only Dobbie
So I assumed it must be a code controlling the Combo Box!

You assumed wrong.

If you have a value list containing the text string "Dobbie" that's exactly
what you'll get in the combo box.

If the value list contains the string "Dobbie, Chris" you'll get that!

I would recommend using a Query with a calculated field. My telepathy is a bit
rusty, but assuming that you have a table of People with fields PersonID,
LastName and FirstName you could create a query

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;

(copy and paste this into the SQL window of a query and adjust the table and
fieldnames to your own objects' names). Save the query and change the combo's
row source type to Table/Query and reference the saved query.

It would have nothing to do with the case that opens the Form would it?
DoCmd.OpenForm "frmInvoiceClient", , , , , , "ModifyOldInvoice"

Nope.
 
B

Bob Vance

Thanks John have tried using a query but I have no control over the text
box,If I change columns to 0cm;5cm;4cm
I do see the two names in I select the drop down arrow

The Row Source is 18;Dobbie, Chris
No Control Source
In the code is : cbOwnerName.RowSourceType = "Value List"
Thanks Bob

John W. Vinson said:
Thanks Jeff it does have a Value List, but what ever way I change it I can
not get Dobbie, Chris Only Dobbie
So I assumed it must be a code controlling the Combo Box!

You assumed wrong.

If you have a value list containing the text string "Dobbie" that's
exactly
what you'll get in the combo box.

If the value list contains the string "Dobbie, Chris" you'll get that!

I would recommend using a Query with a calculated field. My telepathy is a
bit
rusty, but assuming that you have a table of People with fields PersonID,
LastName and FirstName you could create a query

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;

(copy and paste this into the SQL window of a query and adjust the table
and
fieldnames to your own objects' names). Save the query and change the
combo's
row source type to Table/Query and reference the saved query.

It would have nothing to do with the case that opens the Form would it?
DoCmd.OpenForm "frmInvoiceClient", , , , , , "ModifyOldInvoice"

Nope.
 
J

John W. Vinson

Thanks John have tried using a query but I have no control over the text
box,If I change columns to 0cm;5cm;4cm
I do see the two names in I select the drop down arrow

Reread my message.

You have a "Value List".

That is wrong.

You must change that from "Value List" to "Table/Query".

You must also create the query (I hope that you have a table of names.... do
you?)
 
B

Bob Vance

Thanks John , I have tried that many times, I just get error after error by
making the query the row source this is one of my errors
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
and still only shows Dobbie in the combo box, I will give up on this one
thanks Bob
 
S

Steve

Bob,

Did you try what I suggested?

Steve


Bob Vance said:
Thanks John , I have tried that many times, I just get error after error
by making the query the row source this is one of my errors
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
and still only shows Dobbie in the combo box, I will give up on this one
thanks Bob
 
J

John W. Vinson

Thanks John , I have tried that many times, I just get error after error by
making the query the row source this is one of my errors
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
and still only shows Dobbie in the combo box, I will give up on this one
thanks Bob

Bob, if you would actually USE the advice that Steve and I are offering, you
would not have this problem.

Frankly, it's getting very frustrating - you don't answer our questions and
you clearly are not following our advice. I'll try once more. Please answer
the following questions:

1. What is the structure of your table of names? What are the fieldnames and
datatypes? Please list them in the format

PersonID Autonumber Primary Key
LastName Text
FirstName Text

using your own fieldnames.

2. What Query did you use for the rowsource? Please open the query in SQL view
and copy and paste the entire SQL to a message here.

3. You say you got lots of errors. WHAT ERRORS? "Doctor, I don't feel good,
what should I take?" isn't going to get a good diagnosis.

4. You post a line of code out of context. Please post the complete VBA code
and indicate what the various controls do.

5. Did you *try* concatenating the first and last names into a calculated
field in the query, as both Steve and I suggested? Again:

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;
 
B

Bob Vance

John W. Vinson said:
Bob, if you would actually USE the advice that Steve and I are offering,
you
would not have this problem.

Frankly, it's getting very frustrating - you don't answer our questions
and
you clearly are not following our advice. I'll try once more. Please
answer
the following questions:

1. What is the structure of your table of names? What are the fieldnames
and
datatypes? Please list them in the format
ClientID (Number)
LastName (Text)
FirstName (Text)
using your own fieldnames.

2. What Query did you use for the rowsource? Please open the query in SQL
view
and copy and paste the entire SQL to a message here.
SELECT tblOwnerInfo_OwnerID,
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName) AS
ClientName, tblOwnerInfo_OwnerFirstName, tblOwnerInfo_OwnerLastName,
tblOwnerInfo.Status
FROM tblOwnerInfo
ORDER BY
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName);
3. You say you got lots of errors. WHAT ERRORS? "Doctor, I don't feel
good,
what should I take?" isn't going to get a good diagnosis.
I am not getting any Errors now when I use the query for the Combo Box but
it is having no effect on the result showing still just getting "Doddie"
4. You post a line of code out of context. Please post the complete VBA
code
and indicate what the various controls do.
Private Sub subShowInvoiceValues()
subBlankForm
With recInvoice

tbInvoiceID.value = .Fields("InvoiceID")
lngInvoiceID = tbInvoiceID.value
cbOwnerName.RowSource = .Fields("OwnerID") & ";" &
..Fields("OwnerName")
If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True Then
cbOwnerName.value = .Fields("OwnerID")
Else
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
End If
tbAddress.value = .Fields("OwnerAddress")
tbFatherName.value = .Fields("FatherName")
tbMotherName.value = .Fields("MotherName")
tbClientDetail.value = .Fields("ClientDetail")
If .Fields("DateOfBirth") = "" Or IsNull(.Fields("DateOfBirth")) =
True Then
tbDateOfBirth.value = ""
Else
tbDateOfBirth.value = .Fields("DateOfBirth")
End If
tbSex.value = .Fields("Sex")
cbGSTOptions.value = .Fields("GSTOptionsText")
tbGSTOptionsValue.value = .Fields("GSTOptionsValue")
tbSubTotal.value = .Fields("SubTotal")
tbTotalAmount.value = .Fields("TotalAmount")
tbInvoiceDate.value = IIf(IsNull(.Fields("InvoiceDate")), "",
Format(CDate(.Fields("InvoiceDate")), "dd-mmm-yy"))
chkByCheque.value = IIf(.Fields("InvoiceNo") = "" Or
IsNull(.Fields("InvoiceNo")) Or .Fields("InvoiceNo") = 0, False, True)
End With
End Sub

--------------------------------------------
Private Sub Form_Open(Cancel As Integer)





Set recInvoice = New ADODB.Recordset
If IsNull(Me.tbInvoiceDate) Then

Me!tbInvoiceDate = Now()


Me.Caption = "New Invoice"
cmdModify.Visible = False
cmdPrint.Visible = False
lbInvoiceID.Visible = False
tbInvoiceID.Visible = False
lbInvoiceDate.Visible = False
chkByCheque.Visible = False
lbByCheque.Visible = False

lbInvoiceDate.Visible = True
tbInvoiceDate.Visible = True
cbOwnerName.RowSourceType = "Value List"


If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True Then
cbOwnerName.RowSource = Form_frmActiveHorses.cboClient & ";" &
Form_frmActiveHorses.cboClient.Column(1)
cbOwnerName.value = Forms!frmActiveHorses!cboClient.Column(1)
recInvoice.Open "SELECT * FROM tblInvoice where OwnerName='" &
cbOwnerName.value & "';", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recInvoice.BOF = False And recInvoice.EOF = False Then
If recInvoice.Fields("OwnerAddress") = "" Or
IsNull(recInvoice.Fields("OwnerAddress")) Then
recInvoice.Fields("OwnerAddress") = ""
Else
tbAddress.value = recInvoice.Fields("OwnerAddress")
End If
End If

cmdAdd_Click
'cbOwnerName_AfterUpdate
DoCmd.Close acForm, "frmActiveHorses"
Else 'findthis
recInvoice.Open "SELECT * FROM tblInvoice where InvoiceID=" &
Form_frmModifyInvoiceClient.lstModify.value _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
subShowInvoiceValues
subShowInvoiceDetailValues

If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.value = Form_frmActiveHorses.cboClient.value
Else
cbOwnerName.value = recInvoice.Fields("OwnerID")
End If

bModify = True

Dim dDate As Date
Dim dtDiff
Dim nCountDaysOfYear As Long
Dim nLeapYearNow As Long, nLeapYearOfBillYear As Long
Dim nTotalDays As Long
dDate = Form_frmModifyInvoiceClient.lstModify.Column(2)

dtDiff = DateDiff("d", Format(dDate, "dd/mm/yyyy"),
Format(Now(), "dd/mm/yyyy"))

nLeapYearOfBillYear = DatePart("yyyy", Format(dDate,
"dd/mm/yyyy"))
nLeapYearOfBillYear = nLeapYearOfBillYear Mod 4

nLeapYearNow = DatePart("yyyy", Format(Now(), "dd/mm/yyyy"))
nLeapYearNow = nLeapYearNow Mod 4

If nLeapYearNow = 0 Or nLeapYearOfBillYear = 0 Then
nTotalDays = 180
Else
nTotalDays = 180
End If
If dtDiff > nTotalDays Then
cmdClose.SetFocus
subLockControls False, True
bLockFlag = True
Else
subLockControls True, False
End If

End If
End If
End Sub
5. Did you *try* concatenating the first and last names into a calculated
field in the query, as both Steve and I suggested? Again:

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;
Yes as shown
Thanks for your help.........Bob
 
S

Steve

Bob,

If you still only show Dobbie in the combo box, you still haven't followed
John's and my advise exactly!

Steve


Bob Vance said:
John W. Vinson said:
Bob, if you would actually USE the advice that Steve and I are offering,
you
would not have this problem.

Frankly, it's getting very frustrating - you don't answer our questions
and
you clearly are not following our advice. I'll try once more. Please
answer
the following questions:

1. What is the structure of your table of names? What are the fieldnames
and
datatypes? Please list them in the format
ClientID (Number)
LastName (Text)
FirstName (Text)
using your own fieldnames.

2. What Query did you use for the rowsource? Please open the query in SQL
view
and copy and paste the entire SQL to a message here.
SELECT tblOwnerInfo_OwnerID,
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName) AS
ClientName, tblOwnerInfo_OwnerFirstName, tblOwnerInfo_OwnerLastName,
tblOwnerInfo.Status
FROM tblOwnerInfo
ORDER BY
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName);
3. You say you got lots of errors. WHAT ERRORS? "Doctor, I don't feel
good,
what should I take?" isn't going to get a good diagnosis.
I am not getting any Errors now when I use the query for the Combo Box but
it is having no effect on the result showing still just getting "Doddie"
4. You post a line of code out of context. Please post the complete VBA
code
and indicate what the various controls do.
Private Sub subShowInvoiceValues()
subBlankForm
With recInvoice

tbInvoiceID.value = .Fields("InvoiceID")
lngInvoiceID = tbInvoiceID.value
cbOwnerName.RowSource = .Fields("OwnerID") & ";" &
.Fields("OwnerName")
If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True Then
cbOwnerName.value = .Fields("OwnerID")
Else
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
End If
tbAddress.value = .Fields("OwnerAddress")
tbFatherName.value = .Fields("FatherName")
tbMotherName.value = .Fields("MotherName")
tbClientDetail.value = .Fields("ClientDetail")
If .Fields("DateOfBirth") = "" Or IsNull(.Fields("DateOfBirth")) =
True Then
tbDateOfBirth.value = ""
Else
tbDateOfBirth.value = .Fields("DateOfBirth")
End If
tbSex.value = .Fields("Sex")
cbGSTOptions.value = .Fields("GSTOptionsText")
tbGSTOptionsValue.value = .Fields("GSTOptionsValue")
tbSubTotal.value = .Fields("SubTotal")
tbTotalAmount.value = .Fields("TotalAmount")
tbInvoiceDate.value = IIf(IsNull(.Fields("InvoiceDate")), "",
Format(CDate(.Fields("InvoiceDate")), "dd-mmm-yy"))
chkByCheque.value = IIf(.Fields("InvoiceNo") = "" Or
IsNull(.Fields("InvoiceNo")) Or .Fields("InvoiceNo") = 0, False, True)
End With
End Sub

--------------------------------------------
Private Sub Form_Open(Cancel As Integer)





Set recInvoice = New ADODB.Recordset
If IsNull(Me.tbInvoiceDate) Then

Me!tbInvoiceDate = Now()


Me.Caption = "New Invoice"
cmdModify.Visible = False
cmdPrint.Visible = False
lbInvoiceID.Visible = False
tbInvoiceID.Visible = False
lbInvoiceDate.Visible = False
chkByCheque.Visible = False
lbByCheque.Visible = False

lbInvoiceDate.Visible = True
tbInvoiceDate.Visible = True
cbOwnerName.RowSourceType = "Value List"


If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True Then
cbOwnerName.RowSource = Form_frmActiveHorses.cboClient & ";" &
Form_frmActiveHorses.cboClient.Column(1)
cbOwnerName.value = Forms!frmActiveHorses!cboClient.Column(1)
recInvoice.Open "SELECT * FROM tblInvoice where OwnerName='" &
cbOwnerName.value & "';", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recInvoice.BOF = False And recInvoice.EOF = False Then
If recInvoice.Fields("OwnerAddress") = "" Or
IsNull(recInvoice.Fields("OwnerAddress")) Then
recInvoice.Fields("OwnerAddress") = ""
Else
tbAddress.value = recInvoice.Fields("OwnerAddress")
End If
End If

cmdAdd_Click
'cbOwnerName_AfterUpdate
DoCmd.Close acForm, "frmActiveHorses"
Else 'findthis
recInvoice.Open "SELECT * FROM tblInvoice where InvoiceID=" &
Form_frmModifyInvoiceClient.lstModify.value _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
subShowInvoiceValues
subShowInvoiceDetailValues

If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.value = Form_frmActiveHorses.cboClient.value
Else
cbOwnerName.value = recInvoice.Fields("OwnerID")
End If

bModify = True

Dim dDate As Date
Dim dtDiff
Dim nCountDaysOfYear As Long
Dim nLeapYearNow As Long, nLeapYearOfBillYear As Long
Dim nTotalDays As Long
dDate = Form_frmModifyInvoiceClient.lstModify.Column(2)

dtDiff = DateDiff("d", Format(dDate, "dd/mm/yyyy"),
Format(Now(), "dd/mm/yyyy"))

nLeapYearOfBillYear = DatePart("yyyy", Format(dDate,
"dd/mm/yyyy"))
nLeapYearOfBillYear = nLeapYearOfBillYear Mod 4

nLeapYearNow = DatePart("yyyy", Format(Now(), "dd/mm/yyyy"))
nLeapYearNow = nLeapYearNow Mod 4

If nLeapYearNow = 0 Or nLeapYearOfBillYear = 0 Then
nTotalDays = 180
Else
nTotalDays = 180
End If
If dtDiff > nTotalDays Then
cmdClose.SetFocus
subLockControls False, True
bLockFlag = True
Else
subLockControls True, False
End If

End If
End If
End Sub
5. Did you *try* concatenating the first and last names into a calculated
field in the query, as both Steve and I suggested? Again:

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;
Yes as shown
Thanks for your help.........Bob
 
B

Bob Vance

Well Maybe it might be easier to force a text box to show the name using the
ClientID in Column(0)

I did I change the Row Sourse to my Query,Type to Table/Query, No Control
Source
Bound Column 1, Column Count 2 and 0cm;2.54cm my Columns to no avail
Regards Bob

Steve said:
Bob,

If you still only show Dobbie in the combo box, you still haven't followed
John's and my advise exactly!

Steve


Bob Vance said:
John W. Vinson said:
Thanks John , I have tried that many times, I just get error after error
by
making the query the row source this is one of my errors
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
and still only shows Dobbie in the combo box, I will give up on this one
thanks Bob

Bob, if you would actually USE the advice that Steve and I are offering,
you
would not have this problem.

Frankly, it's getting very frustrating - you don't answer our questions
and
you clearly are not following our advice. I'll try once more. Please
answer
the following questions:

1. What is the structure of your table of names? What are the fieldnames
and
datatypes? Please list them in the format
ClientID (Number)
LastName (Text)
FirstName (Text)
using your own fieldnames.

2. What Query did you use for the rowsource? Please open the query in
SQL view
and copy and paste the entire SQL to a message here.
SELECT tblOwnerInfo_OwnerID,
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName)
AS ClientName, tblOwnerInfo_OwnerFirstName, tblOwnerInfo_OwnerLastName,
tblOwnerInfo.Status
FROM tblOwnerInfo
ORDER BY
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName);
3. You say you got lots of errors. WHAT ERRORS? "Doctor, I don't feel
good,
what should I take?" isn't going to get a good diagnosis.
I am not getting any Errors now when I use the query for the Combo Box
but it is having no effect on the result showing still just getting
"Doddie"
4. You post a line of code out of context. Please post the complete VBA
code
and indicate what the various controls do.
Private Sub subShowInvoiceValues()
subBlankForm
With recInvoice

tbInvoiceID.value = .Fields("InvoiceID")
lngInvoiceID = tbInvoiceID.value
cbOwnerName.RowSource = .Fields("OwnerID") & ";" &
.Fields("OwnerName")
If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.value = .Fields("OwnerID")
Else
cbOwnerName.value =
Form_frmModifyInvoiceClient.lstModify.value
End If
tbAddress.value = .Fields("OwnerAddress")
tbFatherName.value = .Fields("FatherName")
tbMotherName.value = .Fields("MotherName")
tbClientDetail.value = .Fields("ClientDetail")
If .Fields("DateOfBirth") = "" Or IsNull(.Fields("DateOfBirth"))
= True Then
tbDateOfBirth.value = ""
Else
tbDateOfBirth.value = .Fields("DateOfBirth")
End If
tbSex.value = .Fields("Sex")
cbGSTOptions.value = .Fields("GSTOptionsText")
tbGSTOptionsValue.value = .Fields("GSTOptionsValue")
tbSubTotal.value = .Fields("SubTotal")
tbTotalAmount.value = .Fields("TotalAmount")
tbInvoiceDate.value = IIf(IsNull(.Fields("InvoiceDate")), "",
Format(CDate(.Fields("InvoiceDate")), "dd-mmm-yy"))
chkByCheque.value = IIf(.Fields("InvoiceNo") = "" Or
IsNull(.Fields("InvoiceNo")) Or .Fields("InvoiceNo") = 0, False, True)
End With
End Sub

--------------------------------------------
Private Sub Form_Open(Cancel As Integer)





Set recInvoice = New ADODB.Recordset
If IsNull(Me.tbInvoiceDate) Then

Me!tbInvoiceDate = Now()


Me.Caption = "New Invoice"
cmdModify.Visible = False
cmdPrint.Visible = False
lbInvoiceID.Visible = False
tbInvoiceID.Visible = False
lbInvoiceDate.Visible = False
chkByCheque.Visible = False
lbByCheque.Visible = False

lbInvoiceDate.Visible = True
tbInvoiceDate.Visible = True
cbOwnerName.RowSourceType = "Value List"


If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True Then
cbOwnerName.RowSource = Form_frmActiveHorses.cboClient & ";" &
Form_frmActiveHorses.cboClient.Column(1)
cbOwnerName.value = Forms!frmActiveHorses!cboClient.Column(1)
recInvoice.Open "SELECT * FROM tblInvoice where OwnerName='" &
cbOwnerName.value & "';", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recInvoice.BOF = False And recInvoice.EOF = False Then
If recInvoice.Fields("OwnerAddress") = "" Or
IsNull(recInvoice.Fields("OwnerAddress")) Then
recInvoice.Fields("OwnerAddress") = ""
Else
tbAddress.value = recInvoice.Fields("OwnerAddress")
End If
End If

cmdAdd_Click
'cbOwnerName_AfterUpdate
DoCmd.Close acForm, "frmActiveHorses"
Else 'findthis
recInvoice.Open "SELECT * FROM tblInvoice where InvoiceID=" &
Form_frmModifyInvoiceClient.lstModify.value _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
subShowInvoiceValues
subShowInvoiceDetailValues

If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.value = Form_frmActiveHorses.cboClient.value
Else
cbOwnerName.value = recInvoice.Fields("OwnerID")
End If

bModify = True

Dim dDate As Date
Dim dtDiff
Dim nCountDaysOfYear As Long
Dim nLeapYearNow As Long, nLeapYearOfBillYear As Long
Dim nTotalDays As Long
dDate = Form_frmModifyInvoiceClient.lstModify.Column(2)

dtDiff = DateDiff("d", Format(dDate, "dd/mm/yyyy"),
Format(Now(), "dd/mm/yyyy"))

nLeapYearOfBillYear = DatePart("yyyy", Format(dDate,
"dd/mm/yyyy"))
nLeapYearOfBillYear = nLeapYearOfBillYear Mod 4

nLeapYearNow = DatePart("yyyy", Format(Now(), "dd/mm/yyyy"))
nLeapYearNow = nLeapYearNow Mod 4

If nLeapYearNow = 0 Or nLeapYearOfBillYear = 0 Then
nTotalDays = 180
Else
nTotalDays = 180
End If
If dtDiff > nTotalDays Then
cmdClose.SetFocus
subLockControls False, True
bLockFlag = True
Else
subLockControls True, False
End If

End If
End If
End Sub
5. Did you *try* concatenating the first and last names into a
calculated
field in the query, as both Steve and I suggested? Again:

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;
Yes as shown
Thanks for your help.........Bob
 
J

John W. Vinson

I did I change the Row Sourse to my Query,Type to Table/Query, No Control
Source
Bound Column 1, Column Count 2 and 0cm;2.54cm my Columns to no avail
Regards Bob

Try simplifying the query to

SELECT tblOwnerInfo_OwnerID, ([OwnerLastName] + ", ") & [OwnerFirstName]
AS ClientName
FROM tblOwnerInfo
ORDER BY OwnerLastName,OwnerFirstName;

and doublecheck that Dobbie in fact does have a first name in the table.
 
S

Steve

Why did you change the second Column Width from 7cm to 2.54cm? Perhaps you
are getting Dobbie, Chris but your combobox is not wide enough to display
it.

Steve


Bob Vance said:
Well Maybe it might be easier to force a text box to show the name using
the ClientID in Column(0)

I did I change the Row Sourse to my Query,Type to Table/Query, No Control
Source
Bound Column 1, Column Count 2 and 0cm;2.54cm my Columns to no avail
Regards Bob

Steve said:
Bob,

If you still only show Dobbie in the combo box, you still haven't
followed John's and my advise exactly!

Steve


Bob Vance said:
Thanks John , I have tried that many times, I just get error after
error by
making the query the row source this is one of my errors
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
and still only shows Dobbie in the combo box, I will give up on this
one
thanks Bob

Bob, if you would actually USE the advice that Steve and I are
offering, you
would not have this problem.

Frankly, it's getting very frustrating - you don't answer our questions
and
you clearly are not following our advice. I'll try once more. Please
answer
the following questions:

1. What is the structure of your table of names? What are the
fieldnames and
datatypes? Please list them in the format

ClientID (Number)
LastName (Text)
FirstName (Text)

using your own fieldnames.

2. What Query did you use for the rowsource? Please open the query in
SQL view
and copy and paste the entire SQL to a message here.

SELECT tblOwnerInfo_OwnerID,
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName &
', ') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName)
AS ClientName, tblOwnerInfo_OwnerFirstName, tblOwnerInfo_OwnerLastName,
tblOwnerInfo.Status
FROM tblOwnerInfo
ORDER BY
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName &
', ') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName);

3. You say you got lots of errors. WHAT ERRORS? "Doctor, I don't feel
good,
what should I take?" isn't going to get a good diagnosis.
I am not getting any Errors now when I use the query for the Combo Box
but it is having no effect on the result showing still just getting
"Doddie"
4. You post a line of code out of context. Please post the complete VBA
code
and indicate what the various controls do.
Private Sub subShowInvoiceValues()
subBlankForm
With recInvoice

tbInvoiceID.value = .Fields("InvoiceID")
lngInvoiceID = tbInvoiceID.value
cbOwnerName.RowSource = .Fields("OwnerID") & ";" &
.Fields("OwnerName")
If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.value = .Fields("OwnerID")
Else
cbOwnerName.value =
Form_frmModifyInvoiceClient.lstModify.value
End If
tbAddress.value = .Fields("OwnerAddress")
tbFatherName.value = .Fields("FatherName")
tbMotherName.value = .Fields("MotherName")
tbClientDetail.value = .Fields("ClientDetail")
If .Fields("DateOfBirth") = "" Or IsNull(.Fields("DateOfBirth"))
= True Then
tbDateOfBirth.value = ""
Else
tbDateOfBirth.value = .Fields("DateOfBirth")
End If
tbSex.value = .Fields("Sex")
cbGSTOptions.value = .Fields("GSTOptionsText")
tbGSTOptionsValue.value = .Fields("GSTOptionsValue")
tbSubTotal.value = .Fields("SubTotal")
tbTotalAmount.value = .Fields("TotalAmount")
tbInvoiceDate.value = IIf(IsNull(.Fields("InvoiceDate")), "",
Format(CDate(.Fields("InvoiceDate")), "dd-mmm-yy"))
chkByCheque.value = IIf(.Fields("InvoiceNo") = "" Or
IsNull(.Fields("InvoiceNo")) Or .Fields("InvoiceNo") = 0, False, True)
End With
End Sub

--------------------------------------------
Private Sub Form_Open(Cancel As Integer)





Set recInvoice = New ADODB.Recordset
If IsNull(Me.tbInvoiceDate) Then

Me!tbInvoiceDate = Now()


Me.Caption = "New Invoice"
cmdModify.Visible = False
cmdPrint.Visible = False
lbInvoiceID.Visible = False
tbInvoiceID.Visible = False
lbInvoiceDate.Visible = False
chkByCheque.Visible = False
lbByCheque.Visible = False

lbInvoiceDate.Visible = True
tbInvoiceDate.Visible = True
cbOwnerName.RowSourceType = "Value List"


If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.RowSource = Form_frmActiveHorses.cboClient & ";"
& Form_frmActiveHorses.cboClient.Column(1)
cbOwnerName.value = Forms!frmActiveHorses!cboClient.Column(1)
recInvoice.Open "SELECT * FROM tblInvoice where OwnerName='"
& cbOwnerName.value & "';", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recInvoice.BOF = False And recInvoice.EOF = False Then
If recInvoice.Fields("OwnerAddress") = "" Or
IsNull(recInvoice.Fields("OwnerAddress")) Then
recInvoice.Fields("OwnerAddress") = ""
Else
tbAddress.value = recInvoice.Fields("OwnerAddress")
End If
End If

cmdAdd_Click
'cbOwnerName_AfterUpdate
DoCmd.Close acForm, "frmActiveHorses"
Else 'findthis
recInvoice.Open "SELECT * FROM tblInvoice where InvoiceID=" &
Form_frmModifyInvoiceClient.lstModify.value _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
subShowInvoiceValues
subShowInvoiceDetailValues

If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.value = Form_frmActiveHorses.cboClient.value
Else
cbOwnerName.value = recInvoice.Fields("OwnerID")
End If

bModify = True

Dim dDate As Date
Dim dtDiff
Dim nCountDaysOfYear As Long
Dim nLeapYearNow As Long, nLeapYearOfBillYear As Long
Dim nTotalDays As Long
dDate = Form_frmModifyInvoiceClient.lstModify.Column(2)

dtDiff = DateDiff("d", Format(dDate, "dd/mm/yyyy"),
Format(Now(), "dd/mm/yyyy"))

nLeapYearOfBillYear = DatePart("yyyy", Format(dDate,
"dd/mm/yyyy"))
nLeapYearOfBillYear = nLeapYearOfBillYear Mod 4

nLeapYearNow = DatePart("yyyy", Format(Now(), "dd/mm/yyyy"))
nLeapYearNow = nLeapYearNow Mod 4

If nLeapYearNow = 0 Or nLeapYearOfBillYear = 0 Then
nTotalDays = 180
Else
nTotalDays = 180
End If
If dtDiff > nTotalDays Then
cmdClose.SetFocus
subLockControls False, True
bLockFlag = True
Else
subLockControls True, False
End If

End If
End If
End Sub

5. Did you *try* concatenating the first and last names into a
calculated
field in the query, as both Steve and I suggested? Again:

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;
Yes as shown
Thanks for your help.........Bob
 
B

Bob Vance

Thanks Guys, When I drag the combo box out on Design view I can see then
click Form View I see Dobbie,Chris but when i go to save from I am getting
this error "Item cannot be found in the collection corresponding to the
requeste name or ordinal" and it goes back to just Dobbie
Thanks Bob

Steve said:
Why did you change the second Column Width from 7cm to 2.54cm? Perhaps you
are getting Dobbie, Chris but your combobox is not wide enough to display
it.

Steve


Bob Vance said:
Well Maybe it might be easier to force a text box to show the name using
the ClientID in Column(0)

I did I change the Row Sourse to my Query,Type to Table/Query, No Control
Source
Bound Column 1, Column Count 2 and 0cm;2.54cm my Columns to no avail
Regards Bob

Steve said:
Bob,

If you still only show Dobbie in the combo box, you still haven't
followed John's and my advise exactly!

Steve



Thanks John , I have tried that many times, I just get error after
error by
making the query the row source this is one of my errors
cbOwnerName.value = Form_frmModifyInvoiceClient.lstModify.value
and still only shows Dobbie in the combo box, I will give up on this
one
thanks Bob

Bob, if you would actually USE the advice that Steve and I are
offering, you
would not have this problem.

Frankly, it's getting very frustrating - you don't answer our
questions and
you clearly are not following our advice. I'll try once more. Please
answer
the following questions:

1. What is the structure of your table of names? What are the
fieldnames and
datatypes? Please list them in the format

ClientID (Number)
LastName (Text)
FirstName (Text)

using your own fieldnames.

2. What Query did you use for the rowsource? Please open the query in
SQL view
and copy and paste the entire SQL to a message here.

SELECT tblOwnerInfo_OwnerID,
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName &
', ') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName)
AS ClientName, tblOwnerInfo_OwnerFirstName, tblOwnerInfo_OwnerLastName,
tblOwnerInfo.Status
FROM tblOwnerInfo
ORDER BY
IIf(IsNull(tblOwnerInfo_OwnerLastName),'',tblOwnerInfo_OwnerLastName &
', ') & " " &
IIf(IsNull(tblOwnerInfo_OwnerFirstName),'',tblOwnerInfo_OwnerFirstName);

3. You say you got lots of errors. WHAT ERRORS? "Doctor, I don't feel
good,
what should I take?" isn't going to get a good diagnosis.
I am not getting any Errors now when I use the query for the Combo Box
but it is having no effect on the result showing still just getting
"Doddie"
4. You post a line of code out of context. Please post the complete
VBA code
and indicate what the various controls do.
Private Sub subShowInvoiceValues()
subBlankForm
With recInvoice

tbInvoiceID.value = .Fields("InvoiceID")
lngInvoiceID = tbInvoiceID.value
cbOwnerName.RowSource = .Fields("OwnerID") & ";" &
.Fields("OwnerName")
If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.value = .Fields("OwnerID")
Else
cbOwnerName.value =
Form_frmModifyInvoiceClient.lstModify.value
End If
tbAddress.value = .Fields("OwnerAddress")
tbFatherName.value = .Fields("FatherName")
tbMotherName.value = .Fields("MotherName")
tbClientDetail.value = .Fields("ClientDetail")
If .Fields("DateOfBirth") = "" Or
IsNull(.Fields("DateOfBirth")) = True Then
tbDateOfBirth.value = ""
Else
tbDateOfBirth.value = .Fields("DateOfBirth")
End If
tbSex.value = .Fields("Sex")
cbGSTOptions.value = .Fields("GSTOptionsText")
tbGSTOptionsValue.value = .Fields("GSTOptionsValue")
tbSubTotal.value = .Fields("SubTotal")
tbTotalAmount.value = .Fields("TotalAmount")
tbInvoiceDate.value = IIf(IsNull(.Fields("InvoiceDate")), "",
Format(CDate(.Fields("InvoiceDate")), "dd-mmm-yy"))
chkByCheque.value = IIf(.Fields("InvoiceNo") = "" Or
IsNull(.Fields("InvoiceNo")) Or .Fields("InvoiceNo") = 0, False, True)
End With
End Sub

--------------------------------------------
Private Sub Form_Open(Cancel As Integer)





Set recInvoice = New ADODB.Recordset
If IsNull(Me.tbInvoiceDate) Then

Me!tbInvoiceDate = Now()


Me.Caption = "New Invoice"
cmdModify.Visible = False
cmdPrint.Visible = False
lbInvoiceID.Visible = False
tbInvoiceID.Visible = False
lbInvoiceDate.Visible = False
chkByCheque.Visible = False
lbByCheque.Visible = False

lbInvoiceDate.Visible = True
tbInvoiceDate.Visible = True
cbOwnerName.RowSourceType = "Value List"


If CurrentProject.AllForms("frmActiveHorses").IsLoaded = True
Then
cbOwnerName.RowSource = Form_frmActiveHorses.cboClient & ";"
& Form_frmActiveHorses.cboClient.Column(1)
cbOwnerName.value =
Forms!frmActiveHorses!cboClient.Column(1)
recInvoice.Open "SELECT * FROM tblInvoice where OwnerName='"
& cbOwnerName.value & "';", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recInvoice.BOF = False And recInvoice.EOF = False Then
If recInvoice.Fields("OwnerAddress") = "" Or
IsNull(recInvoice.Fields("OwnerAddress")) Then
recInvoice.Fields("OwnerAddress") = ""
Else
tbAddress.value = recInvoice.Fields("OwnerAddress")
End If
End If

cmdAdd_Click
'cbOwnerName_AfterUpdate
DoCmd.Close acForm, "frmActiveHorses"
Else 'findthis
recInvoice.Open "SELECT * FROM tblInvoice where InvoiceID="
& Form_frmModifyInvoiceClient.lstModify.value _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
subShowInvoiceValues
subShowInvoiceDetailValues

If CurrentProject.AllForms("frmActiveHorses").IsLoaded =
True Then
cbOwnerName.value = Form_frmActiveHorses.cboClient.value
Else
cbOwnerName.value = recInvoice.Fields("OwnerID")
End If

bModify = True

Dim dDate As Date
Dim dtDiff
Dim nCountDaysOfYear As Long
Dim nLeapYearNow As Long, nLeapYearOfBillYear As Long
Dim nTotalDays As Long
dDate = Form_frmModifyInvoiceClient.lstModify.Column(2)

dtDiff = DateDiff("d", Format(dDate, "dd/mm/yyyy"),
Format(Now(), "dd/mm/yyyy"))

nLeapYearOfBillYear = DatePart("yyyy", Format(dDate,
"dd/mm/yyyy"))
nLeapYearOfBillYear = nLeapYearOfBillYear Mod 4

nLeapYearNow = DatePart("yyyy", Format(Now(), "dd/mm/yyyy"))
nLeapYearNow = nLeapYearNow Mod 4

If nLeapYearNow = 0 Or nLeapYearOfBillYear = 0 Then
nTotalDays = 180
Else
nTotalDays = 180
End If
If dtDiff > nTotalDays Then
cmdClose.SetFocus
subLockControls False, True
bLockFlag = True
Else
subLockControls True, False
End If

End If
End If
End Sub

5. Did you *try* concatenating the first and last names into a
calculated
field in the query, as both Steve and I suggested? Again:

SELECT PersonID, [LastName] & ", " & [FirstName] FROM PEOPLE ORDER BY
LastName, FirstName;
Yes as shown
Thanks for your help.........Bob
 
J

John W. Vinson

Thanks Guys, When I drag the combo box out on Design view I can see then
click Form View I see Dobbie,Chris but when i go to save from I am getting
this error "Item cannot be found in the collection corresponding to the
requeste name or ordinal" and it goes back to just Dobbie
Thanks Bob

Sounds like the form has become (or is becoming) corrupt, possibly because
you've been trying so many things.

Make sure that Name Autocorrect is turned OFF (it can mess things up), create
a new, empty form, and copy and paste all the controls except this combo box
onto it. Delete the original form; compact and repair the database, and
recreate this combo, and see if that restores functionality.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top