UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Correct Handling Of Composite Items And Inventory. Please Help!, Access 2013    
 
   
soygestion
post Dec 5 2018, 10:41 AM
Post#1



Posts: 6
Joined: 5-December 18



Hello everyone and thanks for your help.
I have a problem that I describe below.
I am making invoices whose detail (BILLED ITEMS) is stored in the F_LFA table. The billed items can in turn have compounds (combos) that is to say be composed of other

articles that also affect the inventory, for example:
-------------------------------------------------
Article code Code of its composition
(Text type) (Text type)
-------------------------------------------------
C1 C9901
C1 C9902
C2 Does not have compounds
C3 C99100
C3 C99101
C3 C99102

I already managed to go discounting the stock table (F_STO) The articles (both the main ones (C1, C2, C3) and their compounds (C9901, C9902, C99100 ETC.), each time I
append that article to the detail (F_LFA).
The problem I have now arises when I have to cancel the COMPLETE bill (P.e in the event that the customer cancel the purchase). In this case, he would have to
"reverse" the whole procedure, that is: to go through the detail of the invoice (F_LFA), to eliminate each one of his records and to return the stock to where it was
before. I am doing it this way, but it does not do it correctly and it throws me the error '3021' There is NO active record.


' The code, I call the procedure on the click of the <Cancel> button.
' -------------------------------------------------------------------

Private Sub ActualizaStoc13()

Dim DB As DAO.Database
Dim RsL As DAO.Recordset
Dim SqlL As String

' compounds
Dim FactID As Integer
Dim codArt As String
Dim a As String
Dim Cant As Integer

On Error GoTo ErrorHandler

Set DB = CurrentDb

SqlL = "select * from f_lfa_c where TIPLFA= '" & Me.TxtTipo.Value & "' AND codlfa= " & Me.TxtNumero.Value

Set RsL = DB.OpenRecordset(SqlL, dbOpenDynaset)

codArt = RsL!ARTLFA
FactID = Me.TxtNumero.Value
Cant = RsL!CANLFA 'Product quantity
a = DLookup("ARTLFA", "[F_LFA_c]", "ARTLFA='" & codArt & "' AND CODLFA=" & FactID)

If RsL.EOF Then Exit Sub

RsL.MoveFirst

Do Until RsL.EOF

codArt = RsL!ARTLFA
' simple provisional notice to go see the procedure

MsgBox "Articulos en Factura : " & RsL!ARTLFA & " - " & RsL!DESLFA
' We discount each compound of the selected article by means of the function: <updateSto>
updateSto codArt, Cant
'-----------
RsL.Delete
RsL.MoveNext

' simple provisional notice to go see the procedure
MsgBox "Articulos en Factura (**siguiente) : " & RsL!ARTLFA & " - " & RsL!DESLFA ' Here it shows the error '3021'
Loop
RsL.Close
Set RsL = Nothing
Set DB = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

'------------------------
'The <updateSto> function
'------------------------
Public Sub updateSto(cod As String, cantF As Integer)
On Error GoTo errHand

Dim DB As DAO.Database
Dim rsCom As Recordset
Dim StrSQL_COM As String
Dim rsSto As Recordset
Dim StrSQL_Sto As String
Dim RsF As DAO.Recordset


Set DB = CurrentDb()
'Query for the stock (F_STO)
StrSQL_Sto = "SELECT F_STO.ARTSTO, F_STO.ACTSTO, F_STO.DISSTO FROM F_STO;"

Set rsSto = DB.OpenRecordset(StrSQL_Sto)

'Query to load database compound articles
StrSQL_COM = "SELECT F_COM.CODCOM, F_COM.ARTCOM, F_COM.UNICOM" _
& " FROM F_COM WHERE (((F_COM.CODCOM)= '" & cod & "'));"
Set rsCom = DB.OpenRecordset(StrSQL_COM)

'Count how many compounds the raw material has (It should always be greater than 1)

If (rsCom.RecordCount > 0) Then
rsCom.MoveLast
rsCom.MoveFirst
Debug.Print rsCom.RecordCount
Else
End 'If there is no compound finish search
End If


'I check availability in the F_STO of each compound for the raw material
activerecords = rsCom.RecordCount
For i = 1 To activerecords

a = rsCom.Fields("ARTCOM")

rsSto.Filter = "(ARTSTO)= '" & a & "'" 'Filter to only get the compound in the table F_STO

Set RsF = rsSto.OpenRecordset
RsF.Edit
' We add (reverse) the amount invoiced in F_LFA
CantDisponible = RsF.Fields("ACTSTO") + (cantF * rsCom.Fields("UNICOM"))
RsF.Fields("ACTSTO") = CantDisponible
RsF.Fields("DISSTO") = CantDisponible
RsF.Update 'Save the changes made to the F_STO
rsCom.MoveNext 'Move to next composite record
Next i


rsCom.Close
rsSto.Close
RsF.Close
Set rsCom = Nothing
Set rsSto = Nothing
Set DB = Nothing

errHand:

End Sub
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 12:13 PM
Post#2


UA Admin
Posts: 34,084
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

I wonder if it might not be more efficient to do the reversal--i.e. delete records of a cancelled order--using a DELETE query rather than recordsets.

On the other hand, one of the things I always wonder is whether we really want to DELETE records, which effectively destroys any trace of that cancelled order in the database. Maybe, in many cases, it's better to set a flag field, i.e. a boolean or yes/no field, which is changed to reflect cancelled orders. I might call that "IsCancelled" and use it to indicate all cancelled orders. That way, you can still go back and answer questions like "How many cancelled orders did we have last quarter?" Or "What items were ordered but later cancelled?"

Of course, that assumes you want that kind of historical information.

In any event, I suspect you'll get cleaner code with update or delete queries instead of looping recordsets.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
soygestion
post Dec 5 2018, 12:30 PM
Post#3



Posts: 6
Joined: 5-December 18



Thanks for your answer. The truth is that I work with temporary tables ie the final records are not deleted, there is always a trace of generated invoices. The challenge is when the client is on the site, makes an order, I am affecting the inventories of articles and their compounds and the client cancels the order (which often happens in our environment) that is where I must have a procedure that can be foreseen said case. I will try to use update consultations anyway and I will comment. If you think you can help me with the code, it would be very appreciated, since my experience with Queries is not very broad. Thanks again
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 12:35 PM
Post#4


UA Admin
Posts: 34,084
Joined: 20-June 02
From: Newcastle, WA


I see.

Well, a delete query version would be something like:

CODE
SqlL = "DELETE * FROM f_lfa_c where TIPLFA= '" & Me.TxtTipo.Value & "' AND codlfa= " & Me.TxtNumero.Value
CurrentDB.Execute SqlL, DBFailOnError

' repeat for other related tables, as needed.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
projecttoday
post Dec 5 2018, 01:38 PM
Post#5


UtterAccess VIP
Posts: 10,352
Joined: 10-February 04
From: South Charleston, WV


So is f_lfa_c a temporary table? Why do you do MsgBox "Articulos en Factura (**siguiente) : " & RsL!ARTLFA & " - " & RsL!DESLFA ' Here it shows the error '3021' after the .MoveNext?

--------------------
Robert Crouser
Go to the top of the page
 
soygestion
post Dec 5 2018, 02:19 PM
Post#6



Posts: 6
Joined: 5-December 18



Thank you. Table F_LFA_C is where the data is stored until the sale is charged. the msgbox is simply informatic, to see what is happening, I know I could use the window immediately and use debug.print but I preferred to see what the procedure was.
Go to the top of the page
 
soygestion
post Dec 5 2018, 02:20 PM
Post#7



Posts: 6
Joined: 5-December 18



Do you think I could upload a copy of the procedure as an access file to appreciate it better?
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 02:24 PM
Post#8


UA Admin
Posts: 34,084
Joined: 20-June 02
From: Newcastle, WA


Please do so.

Create a demo with just enough sample data to see how you need it to work.

Compact and repair the file(s).

Compress the accdb(s) into a ZIP file.

Upload it using the Upload process.

Attached File  uploadbutton.jpg ( 22.86K )Number of downloads: 1

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
soygestion
post Dec 5 2018, 02:38 PM
Post#9



Posts: 6
Joined: 5-December 18



Okay, I'll do it in the shortest time possible and I'll upload it. Thank you
Go to the top of the page
 
projecttoday
post Dec 5 2018, 03:37 PM
Post#10


UtterAccess VIP
Posts: 10,352
Joined: 10-February 04
From: South Charleston, WV


So you contradicted yourself.

Don't delete from a permanent table.

--------------------
Robert Crouser
Go to the top of the page
 
soygestion
post Dec 5 2018, 04:23 PM
Post#11



Posts: 6
Joined: 5-December 18



I do not seem to contradict myself, I'm going to try to explain the logic of the procedure:

1. I take the order of my client (even the invoice has not been charged) I use the table F_LFA_R to go entering the articles
2. I am affecting the inventory table (F_STO) as the client requests each item, discounting the quantities requested by the client.
3. If the client requests the account, I collect the invoice and transfer the information from the temporary table (F_LFA_R) to the final invoice table (F_LFA)
4. If the client cancels the order I do not make the definite transfer and I must return the inventories to where they were.

I hope to have explained. I'm going to make the demo so that it's explained better. Thank you
Go to the top of the page
 
projecttoday
post Dec 5 2018, 07:11 PM
Post#12


UtterAccess VIP
Posts: 10,352
Joined: 10-February 04
From: South Charleston, WV


CODE
RsL.MoveNext

' simple provisional notice to go see the procedure
MsgBox "Articulos en Factura (**siguiente) : " & RsL!ARTLFA & " - " & RsL!DESLFA ' Here it shows the error '3021'
Loop


Did you mean to put the MsbBox after the MoveNext?

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2018 - 12:06 AM