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

locations in inventory control

P: 9
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
Dec 15 '07 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,656
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.
Dec 16 '07 #2

NeoPa
Expert Mod 15k+
P: 31,656
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.
Dec 16 '07 #3

P: 9
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
Jan 3 '08 #4

NeoPa
Expert Mod 15k+
P: 31,656
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.
Jan 3 '08 #5

P: 9
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
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database, sSQL As String, rs As DAO.Recordset
  2.  
  3.   'Set vars
  4.   Set db = CurrentDb
  5.   DoCmd.SetWarnings False
  6.  
  7.   'Controlling recordset based on Order Detail table but constrained for this specific Order number
  8.   sSQL = "SELECT * FROM transferdetailtbl WHERE transfernoteID = " & Me.transfernoteID
  9.   Set rs = db.OpenRecordset(sSQL)
  10.  
  11.   'Start a loop to load each Order Line item
  12.   While Not rs.EOF 'EOF is End Of File and is a marker to test for the end of the rs
  13.   'Outward movement
  14.    'sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
  15.         '  " VALUES (#" & Date & "#, " & rs![ProductID] & ", " & Me.Cbofromlocation & ", " & rs![transferqty] & ", 0 )"
  16.    'db.Execute sSQL
  17.  
  18.    'Inward movement
  19.    sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
  20.          " VALUES (#" & Date & "#, '" & rs![ProductID] & "', " & Me.CboToLocation & ", 0," & rs![transferqty] & ")"
  21.   db.Execute sSQL
  22.  
  23.  
  24.      'Move onto the next Order Line
  25.      rs.MoveNext
  26.  
  27.   'Go back to the head of the loop
  28.   Wend
  29.  
  30.   'All done, so close recordset and clear memory allocation
  31.   rs.Close
  32.   Set rs = Nothing
  33.  
  34.   'Reset vars
  35.   DoCmd.SetWarnings True
  36.  
  37.   'Confirm completion
  38.   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
Jan 7 '08 #6

NeoPa
Expert Mod 15k+
P: 31,656
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.
Jan 7 '08 #7

P: 9
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
Jan 9 '08 #8

NeoPa
Expert Mod 15k+
P: 31,656
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.
Jan 9 '08 #9

P: 9
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 ?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command18_Click()
  2.  
  3. Dim db As DAO.Database, sSQL As String, rs As DAO.Recordset
  4.  
  5.   'Set vars
  6.   Set db = CurrentDb
  7.   DoCmd.SetWarnings False
  8.  
  9.   'Controlling recordset based on Order Detail table but constrained for this specific Order number
  10.   sSQL = "SELECT * FROM transferdetailtbl WHERE transfernoteID = " & Me.transfernoteID
  11.   Set rs = db.OpenRecordset(sSQL)
  12.  
  13.   'Start a loop to load each Order Line item
  14.   While Not rs.EOF 'EOF is End Of File and is a marker to test for the end of the rs
  15.   'Outward movement
  16.    sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
  17.           " VALUES (#" & Date & "#, " & rs![productID] & ", " & Me.Cbofromlocation & ", " & rs![transferqty] & ", 0 )"
  18.    db.Execute sSQL
  19.  
  20.    'Inward movement
  21.    sSQL = "INSERT INTO tblTransactions (TransactionDate,ProductID,LocationID, UnitsMoved,unitsreceived)" & _
  22.          '" VALUES (#" & Date & "#, '" & rs![ProductID] & "', " & Me.CboToLocation & ", 0," & rs![transferqty] & ")"
  23.   ' db.Execute sSQL
  24.  
  25.  
  26.      'Move onto the next Order Line
  27.      rs.MoveNext
  28.  
  29.   'Go back to the head of the loop
  30.   Wend
  31.  
  32.   'All done, so close recordset and clear memory allocation
  33.   rs.Close
  34.   Set rs = Nothing
  35.  
  36.   'Reset vars
  37.   DoCmd.SetWarnings True
  38.  
  39.   'Confirm completion
  40.   MsgBox "  Goods Received   ", , "Info..."
  41.  
  42. End Sub
Jan 14 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.