locations in inventory control

[Datto]

hello All,
actually it is my first post here ,and i want to thank in advance for any help
i have these tables
tblproduct :- productID,ProductName , etc
tbltransaction:- transactionID,TransactionDate,ProductID,LocatonID, UnitsMoved,unitsrecieved
Locationtbl:-locationID,LocationName,

and i have only three location (receiving ,production,finishing)
i am looking for method that whenever i recieve new products throug my PO form to automatically to be added in the receiving ?
i have TransferForm with two combo boxs (from ,TO ) i need to whenever i fill this form and press on save button to do the movement that selected in the Comboboxs?

because when i solve these issues i want to create querys to calculate the quantity for each location only

hope this was explained well

appreciate any help

[NeoPa]

You appear to have explained the concept of your task, but nowhere do you explain what you're having trouble with, nor what you've tried already.
Without these it's difficult to help.

[NeoPa]

Furthermore, you appear to have posted this same question twice. I'm sure that was a mistake so I will delete the other one for you. Please remember to post your questions only the once in future though.

Welcome to theScripts.

[Datto]

Hello NeoPA

first of sorry for posting my question twice ,,but i thought that no one had read it

and regarding to my question : i need help in the code behind the transfer button to get started ?

thanks ,and happy new year

[NeoPa]

Apology accepted.
Just to be clear though, that's not a valid excuse to post again. A bump after 24 hours is acceptable but simply reposting your question is not.

As far as your question is concerned, you really need to read and respond to the posts. Post #2 particularly says what is needed for us to help. Your response, if it's in response to this, is really not much help.

Please reread it and answer carefully.
Please bear in mind we are not here to provide a free programming resource. Our intention is to assist, particularly with learning. "Doing it for you" is not an option.

[Datto]

Apology accepted.
Just to be clear though, that's not a valid excuse to post again. A bump after 24 hours is acceptable but simply reposting your question is not.

As far as your question is concerned, you really need to read and respond to the posts. Post #2 particularly says what is needed for us to help. Your response, if it's in response to this, is really not much help.

Please reread it and answer carefully.
Please bear in mind we are not here to provide a free programming resource. Our intention is to assist, particularly with learning. "Doing it for you" is not an option.

ok..here is what i have so far

i have these tbls

transactionsTBl
transactionID(PK)
productID (fk)
unit price
units received
unitsmoved
locationID(FK)
transferID

locationTBl
locationID
Locationname

TransferNotetbl
transferNoteID
TransferFrom
transferTo
transferDate

transferDetailtbl
transferDetailID
transferNoteID
productID
transferqty

now i am working on TransferNote form i have created a transfer button and code to transfer items between locations that i have ,and two query 1 to calculate the unitsreceived and another one to calculate the unites moved and this is my code

Dim db As DAO.Database, sSQL As String, rs As DAO.Recordset
 
  'Set vars
  Set db = CurrentDb
  DoCmd.SetWarnings False
 
  'Controlling recordset based on Order Detail table but constrained for this specific Order number
  sSQL = "SELECT * FROM transferdetailtbl WHERE transfernoteID = " & Me.transfernoteID
  Set rs = db.OpenRecordset(sSQL)
 
  'Start a loop to load each Order Line item
  While Not rs.EOF 'EOF is End Of File and is a marker to test for the end of the rs
  'Outward movement
   'sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
        '  " VALUES (#" & Date & "#, " & rs![ProductID] & ", " & Me.Cbofromlocation & ", " & rs![transferqty] & ", 0 )"
   'db.Execute sSQL
 
   'Inward movement
   sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
         " VALUES (#" & Date & "#, '" & rs![ProductID] & "', " & Me.CboToLocation & ", 0," & rs![transferqty] & ")"
  db.Execute sSQL
 
 
     'Move onto the next Order Line
     rs.MoveNext
 
  'Go back to the head of the loop
  Wend
 
  'All done, so close recordset and clear memory allocation
  rs.Close
  Set rs = Nothing
 
  'Reset vars
  DoCmd.SetWarnings True
 
  'Confirm completion
  MsgBox "  Goods Received   ", , "Info..."

now this code is function fine but my problem is that whenever keep clicking on the transferbutton for each transfer not it does the calculation,,i want it to happen only once for each record ???

any tips in that will be highly appreciated

thanks

[NeoPa]

You want the system to stop the operator from transferring the same data more than once from the [TransferNotetbl] and [transferDetailtbl] tables? Presumably ever (not just in a single session).
I would suggest adding a flag to the [TransferNotetbl] table indicating that a record has been copied already. If this flag is found to be true then your code should not include it in the list of items that can be copied. it certainly shouldn't allow it to be copied again.

[Datto]

You want the system to stop the operator from transferring the same data more than once from the [TransferNotetbl] and [transferDetailtbl] tables? Presumably ever (not just in a single session).
I would suggest adding a flag to the [TransferNotetbl] table indicating that a record has been copied already. If this flag is found to be true then your code should not include it in the list of items that can be copied. it certainly shouldn't allow it to be copied again.

yes because it keep adding whenever u click again on the transfer button .
yeah that is what i am looking for ,,something like status (pending or done )
but forgive my weakness can u help me with vba code ?

thanks

[NeoPa]

Have you added a flag field to [TransferNoteTbl]? You need to make a note of the name of that field for your code.
Also, can you post the whole routine please - including the definition (Private Sub ...) and the End Sub line.

[Datto]

Have you added a flag field to [TransferNoteTbl]? You need to make a note of the name of that field for your code.
Also, can you post the whole routine please - including the definition (Private Sub ...) and the End Sub line.

hi ,
sorry for the delay
yes i have added flag field in [transfernoteTbl] named transferstatues and the type Yes/no ,,i want it to be by default No (not transfered ) and the user has to confirm it ,,and once it confirm he can't edit it or to do the transfer again for this record ?

Private Sub Command18_Click()
 
Dim db As DAO.Database, sSQL As String, rs As DAO.Recordset
 
  'Set vars
  Set db = CurrentDb
  DoCmd.SetWarnings False
 
  'Controlling recordset based on Order Detail table but constrained for this specific Order number
  sSQL = "SELECT * FROM transferdetailtbl WHERE transfernoteID = " & Me.transfernoteID
  Set rs = db.OpenRecordset(sSQL)
 
  'Start a loop to load each Order Line item
  While Not rs.EOF 'EOF is End Of File and is a marker to test for the end of the rs
  'Outward movement
   sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
          " VALUES (#" & Date & "#, " & rs![productID] & ", " & Me.Cbofromlocation & ", " & rs![transferqty] & ", 0 )"
   db.Execute sSQL
 
   'Inward movement
   sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
         '" VALUES (#" & Date & "#, '" & rs![ProductID] & "', " & Me.CboToLocation & ", 0," & rs![transferqty] & ")"
  ' db.Execute sSQL
 
 
     'Move onto the next Order Line
     rs.MoveNext
 
  'Go back to the head of the loop
  Wend
 
  'All done, so close recordset and clear memory allocation
  rs.Close
  Set rs = Nothing
 
  'Reset vars
  DoCmd.SetWarnings True
 
  'Confirm completion
  MsgBox "  Goods Received   ", , "Info..."
 
End Sub