Add to a Command Button

[Bob]

This command button sends Horse Invoices In to holding made, but once i
select the Horse and Click the cmdCreateHoldingInvoices_Click()
The horse does not dissapear from the form untill I close it and re-open it,
can something be added to this so as the Horse Disappears straight after
selecting it!
Thanks for any Help................Bob

Private Sub cmdCreateHoldingInvoices_Click()
'It creates the holding Invoices for selected horses.

Dim recInvoice_ItMdt As ADODB.Recordset
Set recInvoice_ItMdt = New ADODB.Recordset
Dim recHorseInfo As ADODB.Recordset
Set recHorseInfo = New ADODB.Recordset
Dim recTmpInvoice_ItMdt As ADODB.Recordset
Set recTmpInvoice_ItMdt = New ADODB.Recordset
Dim lngIntermediateID As Long
Dim nloop As Long

'To Save the record in alphabetical order.
recInvoice_ItMdt.Open "SELECT * FROM tblInvoice_ItMdt", cnnStableAccount,
adOpenDynamic, adLockOptimistic
For nloop = 0 To lstActiveHorses.ListCount - 1
If lstActiveHorses.Selected(nloop) = True Then
Debug.Print lstActiveHorses.Column(1, nloop) 'Prints the Horse Name
Debug.Print lstActiveHorses.Column(0, nloop) 'Prints the Horse Id
recHorseInfo.Open "Select * from tblHorseInfo where HorseID=" _
& lstActiveHorses.Column(0, nloop) & ";", cnnStableAccount,
adOpenDynamic, adLockOptimistic
If recHorseInfo.BOF = False And recHorseInfo.EOF = False Then
With recInvoice_ItMdt
If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF
= False Then
.MoveLast
lngIntermediateID = Nz(.Fields("IntermediateID"), 0)
+ 1
Else
lngIntermediateID = 1
End If
.AddNew
.Fields("IntermediateID") = lngIntermediateID
.Fields("dtDate") = Format(Now, "dd/mm/yyyy")
.Fields("HorseName") = lstActiveHorses.Column(1, nloop)
.Fields("HorseID") = lstActiveHorses.Column(0, nloop)
.Fields("FatherName") =
Nz(recHorseInfo.Fields("FatherName"), "")
.Fields("MotherName") =
Nz(recHorseInfo.Fields("MotherName"), "")
.Fields("HorseDetailInfo") =
Nz(recHorseInfo.Fields("FatherName"), "") _
& "--" & Nz(recHorseInfo.Fields("MotherName"), "") &
"--" _
&
funCalcAge(Format(Nz(recHorseInfo.Fields("DateOfBirth"), "") _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& " -- " & Nz(recHorseInfo.Fields("Sex"), "")
.Fields("Sex") = Nz(recHorseInfo.Fields("Sex"), "")
.Fields("DateOfBirth") =
Nz(recHorseInfo.Fields("DateOfBirth"), "")
'Set the default value to
GSTOptionText field.
.Fields("GSTOptionsText") = "Plus Tax"
.Fields("GSTOptionsValue") = 0
.Fields("SubTotal") = 0
.Fields("TotalAmount") = 0
Application.SysCmd acSysCmdSetStatus, "Horse Name=" &
..Fields("HorseName")
.Update
.Requery
End With
End If

recHorseInfo.Close
End If
Next
Application.SysCmd acSysCmdClearStatus
End Sub

 

[Jeff Boyce]

Bob

Once you've done whatever your code is doing (are you sure you couldn't get
the same done with a simpler query?), you might need to Requery the form (or
the control).

If the form needs requerying, you can use:
Me.Requery

If the control needs requerying, you can use:
Me!YourControl.Requery

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Al Campagna]

Bob,
Is this the case when you've just eneterd the invoice data, and then attempt to print
the invoice?
If so, and I think it is... you should just need to a
Refresh
before printing.
The data you just entered on the form has not been updated to the table yet. Closing
the form, and re-opening it is doing that for you now, Refresh will do that without the
need to close/open.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Bob]

Thanks Jeff BRILLIANT
Me.lstActiveHorses.Requery
Regards Bob

 

[Bob]

Jeff, I do have a problem with this now that I changed it
1. Wont let me multi-select send Invoices, Only one at a time!
2. Wont let me send the last Invoice left
Thanks for any help.............Bob

 

[Jeff Boyce]

Bob

Probably not enough information to offer ideas on #2.

For #1, if you need/want to handle a multi-select situation, you'll need to
modify the code. The gist of it is:

'cycle through the collection of selected items using "For Each xxxxx in
yyyyy"
'during each cycle, process the (single) selected item
'this works even if only one is selected

Check out collections and "For Each ..." in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob]

Its working fine now I added this to my code......................
.Update
.Requery

End With
End If

recHorseInfo.Close

End If

Next
Me.lstActiveHorses.Requery '*****I added this In*******
Application.SysCmd acSysCmdClearStatus
End Sub

Thanks Bob