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
