By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

command button

P: n/a
hi all

first i would like to thank PC Datasheet, i got so far because of
him/her

i need some help again

i have this command button[listing update]

the on-click event procedure is

Private Sub listing_update_Click()
On Error GoTo ErrorHandler
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenQuery "query listing Update", acNormal, acEdit
DoCmd.OpenQuery " query append arc", acNormal, acEdit
Me![list 0u] = 0
Me![list 1n] = 0
Me![list 2r] = 0
Me![list 3m] = 0
Me![list 4m] = 0
Me![list 5d] = 0
ExitHere:
Exit Sub

ErrorHandler:
MsgBox Err.description
Resume ExitHere

End Sub
i would like it to do one more thing
after it enters the 0s in, i need it to
add up the vaues of 6 textboxes [0 untested re], [1 new re], [2
repaired re] . . . . . etc. and if the value is 0 i need to delete the
current record, if it is not 0 just go back to the form.

i tried if -then . . just cant get the sintax (cant even spell it)
right

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Dim intSum As Integer 'change the type if needed
Me![list 5d] = 0
'The Nz's are to change Null values to zero. If not used and any one of the
values
'is Null, then the answer will be Null.
intSum = Nz(Me.[0 untested re],0) + Nz(Me.[1 new re], 0) + Nz(Me.[2 repaired
re],0) + etc
If intSum = 0 Then
'This will delete the current record on the form.
RunCommand acCmdDeleteRecord
End If
ExitHere:

--
Wayne Morgan
MS Access MVP
"gbb0330" <gb*****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com... hi all

first i would like to thank PC Datasheet, i got so far because of
him/her

i need some help again

i have this command button[listing update]

the on-click event procedure is

Private Sub listing_update_Click()
On Error GoTo ErrorHandler
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenQuery "query listing Update", acNormal, acEdit
DoCmd.OpenQuery " query append arc", acNormal, acEdit
Me![list 0u] = 0
Me![list 1n] = 0
Me![list 2r] = 0
Me![list 3m] = 0
Me![list 4m] = 0
Me![list 5d] = 0
ExitHere:
Exit Sub

ErrorHandler:
MsgBox Err.description
Resume ExitHere

End Sub
i would like it to do one more thing
after it enters the 0s in, i need it to
add up the vaues of 6 textboxes [0 untested re], [1 new re], [2
repaired re] . . . . . etc. and if the value is 0 i need to delete the
current record, if it is not 0 just go back to the form.

i tried if -then . . just cant get the sintax (cant even spell it)
right

Nov 13 '05 #2

P: n/a
The only thing that I would guess at would be if the query still had the
record(s) locked at the time the code was running. Instead of the
DoCmd.RunQuery, try

CurrentDb.Execute "query listing Update", dbFailOnError

With the error switch on the end, if the query doesn't work properly, you'll
get an error back. I don't know, but I'm hoping, that this means the code
will have to wait and see if the query finished properly. If that doesn't
work, there is a command that can be used to "flush the buffer" to force all
the queued disk writes to finish and release the record locks.

DBEngine.Idle dbRefreshCache

--
Wayne Morgan
MS Access MVP
"gbb0330" <gb*****@gmail.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
thanks Wayne, it works

now i have another problem, the values in[list 0u],[list 1n] etc.
will not reset to 0 every time, sometimes they will and sometimes they
wont - so far i havent been able to find a patern.

what i am trying to accomplish is to get the number of itmes on hand
from [main table]. the items are clasified in categories from [0
untested re] to [5 dead re]

the user will type in the number of items that were listed for sale on
e-bay from[list 0u] to[list 5d]

then i am using an update query to subtract the number of items listed
from the number of items on hand. the query is called [query listing
update]
for example the query updates the field [0 untested re] to [Main
Table]![0 untested re]-[Main Table]![list 0u]

then i use an append query to keep a record of what has been listed

after that i set the values in[list 0u],[list 1n] etc. to 0
the reason i do that is beacuse - lets say we hav 5 items on hand but
we only want to list 3, the user wants to be able to list some or all
of the items in one listing so they have to type in the number every
time.
and then if there are no more items left on hand the record is
deleated.

any help will be very appreciated

here is the code:

Private Sub listing_update_Click()
On Error GoTo ErrorHandler
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenQuery "query listing Update", acNormal, acEdit

DoCmd.OpenQuery " query append arc", acNormal, acEdit

Me![list 0u] = 0
Me![list 1n] = 0
Me![list 2r] = 0
Me![list 3m] = 0
Me![list 4m] = 0
Me![list 5d] = 0
Me![e-bay number] = Null

Dim intSum As Integer

intSum = Nz(Me.[0 untested re], 0) + Nz(Me.[1 new re], 0) + Nz(Me.[2
repaired re], 0) + Nz(Me.[3 minor defect re], 0) + Nz(Me.[4 major
defect re], 0) + Nz(Me.[5 dead re], 0)
If intSum = 0 Then
'This will delete the current record on the form.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

End If
ExitHere:
Exit Sub

ErrorHandler:
MsgBox Err.description
Resume ExitHere

End Sub

Wayne Morgan wrote:
Dim intSum As Integer 'change the type if needed
> Me![list 5d] = 0
>

'The Nz's are to change Null values to zero. If not used and any one

of the
values
'is Null, then the answer will be Null.
intSum = Nz(Me.[0 untested re],0) + Nz(Me.[1 new re], 0) + Nz(Me.[2

repaired
re],0) + etc
If intSum = 0 Then
'This will delete the current record on the form.
RunCommand acCmdDeleteRecord
End If
>
> ExitHere:

--
Wayne Morgan
MS Access MVP
"gbb0330" <gb*****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
> hi all
>
> first i would like to thank PC Datasheet, i got so far because of
> him/her
>
> i need some help again
>
> i have this command button[listing update]
>
> the on-click event procedure is
>
> Private Sub listing_update_Click()
> On Error GoTo ErrorHandler
> DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 > DoCmd.OpenQuery "query listing Update", acNormal, acEdit
> DoCmd.OpenQuery " query append arc", acNormal, acEdit
> Me![list 0u] = 0
> Me![list 1n] = 0
> Me![list 2r] = 0
> Me![list 3m] = 0
> Me![list 4m] = 0
> Me![list 5d] = 0
>
>
> ExitHere:
> Exit Sub
>
> ErrorHandler:
> MsgBox Err.description
> Resume ExitHere
>
> End Sub
>
>
> i would like it to do one more thing
> after it enters the 0s in, i need it to
> add up the vaues of 6 textboxes [0 untested re], [1 new re], [2
> repaired re] . . . . . etc. and if the value is 0 i need to delete the > current record, if it is not 0 just go back to the form.
>
> i tried if -then . . just cant get the sintax (cant even spell it)
> right
>

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.