In need of a best friend!
in [Help]
|
From: BobbyDazzler on 28 Jan 2010 08:39 I'll try and explain this as best I can! I have a form with one unbound textbox. The user enters the first part of a stock code into the unbound textbox and clicks a "Search" command button. This opens another form with a listbox. The listbox recordsource is a query which selects all of the products (from tblproducts) where the stockcode starts with the characters entered entered in the unbound textbox of the first form. Additionally I utilise Allen Brownes OnHand() function to calculate the stock level of each stock item. In business terms - a customer phones in to get a price for a tyre/ tire (stock item) and i use these forms to locate the stock item, verify we have them in stock and give the customer the price. If the customer then wants to go ahead with the purchase we book them in to get the tyres fitted. "Booking them in" literally involves writing the details in the diary! It can occassionally be up to a week later that the tyres/tires are fitted. The problem with this system is that if we only have 1 of a particular item in stock and someone phones in for that specific item and wants it fitted a week later. Specifically our system will show that we still have the stock available for the week (until we invoice the job). We may sell those tyres to another customer prior to the original enquirer coming back (a week later), then when they turn up we no longer have the tyres they requested in stock! I hope that makes sense! My solution was to be able to single click the specific stock code then click a command button which would ask for a vehicle registration and number of tyres required and then this will automatically create a record in tblinvoice (where vehicle registration is stored) and a linked record in tblinvoicedetail (where products sold are stored) There is obviously more required than that (cost prices/profit customer details etc etc etc) but if someone could show me the basic code to get me started i should be able to amend it to do everything that I want. In a nutshell the code must create a record in tblinvoice with the vehicle registration and a linked record in tblinvoicedetail with the products selected from the listbox. If anyone has a method for adding the products to an existing record in tblinvoice I'll be their best friend forever!!!! Thanks
From: Roger on 28 Jan 2010 11:45 On Jan 28, 6:39 am, BobbyDazzler <david.a.mitch...(a)inbox.com> wrote: > I'll try and explain this as best I can! > > I have a form with one unbound textbox. The user enters the first > part of a stock code into the unbound textbox and clicks a "Search" > command button. This opens another form with a listbox. The listbox > recordsource is a query which selects all of the products (from > tblproducts) where the stockcode starts with the characters entered > entered in the unbound textbox of the first form. Additionally I > utilise Allen Brownes OnHand() function to calculate the stock level > of each stock item. > > In business terms - a customer phones in to get a price for a tyre/ > tire (stock item) and i use these forms to locate the stock item, > verify we have them in stock and give the customer the price. If the > customer then wants to go ahead with the purchase we book them in to > get the tyres fitted. "Booking them in" literally involves writing > the details in the diary! It can occassionally be up to a week later > that the tyres/tires are fitted. > > The problem with this system is that if we only have 1 of a particular > item in stock and someone phones in for that specific item and wants > it fitted a week later. Specifically our system will show that we > still have the stock available for the week (until we invoice the > job). We may sell those tyres to another customer prior to the > original enquirer coming back (a week later), then when they turn up > we no longer have the tyres they requested in stock! > > I hope that makes sense! > > My solution was to be able to single click the specific stock code > then click a command button which would ask for a vehicle registration > and number of tyres required and then this will automatically create a > record in tblinvoice (where vehicle registration is stored) and a > linked record in tblinvoicedetail (where products sold are stored) > > There is obviously more required than that (cost prices/profit > customer details etc etc etc) but if someone could show me the basic > code to get me started i should be able to amend it to do everything > that I want. > > In a nutshell the code must create a record in tblinvoice with the > vehicle registration and a linked record in tblinvoicedetail with the > products selected from the listbox. If anyone has a method for adding > the products to an existing record in tblinvoice I'll be their best > friend forever!!!! > > Thanks so you have tblInvoice invoiceId autonumber vehiculeRegistration text tblInvoiceDetail invoiceId lineNum productId so your command button click() event will do dim rs as recordset dim lngInvoiceId as long set rs = currentdb.openrecordset("tblnvoice") with rs .addNew !vehiculeRegistration = something .update lnginvoiceid = !invoiceId end with set rs = currentdb.openrecordset("tblInvoiceDetail") with rs .addnew !invoiceid = lnginvoiceid !lineNum = 1 !productId = some tire .update end with
From: BobbyDazzler on 29 Jan 2010 17:50 Roger, you are officially my best friend forever! Just a couple of questions, if thats ok? Why do you use line numbers in tblinvoicedetail? I would of thought that the invdetailid autonumber would have been sufficient? This line doesn't work : - lnginvoiceid = !invoiceId it just adds the invoiceid of the first record in tblinvoices everytime so i added a llittle code to get the max record which seems to work fine. Is there maybe some sort of requery required after the .update? From your code I was even able to amend it to allow me to add the product to an existing record so you've helped kill 2 birds with one stone! Very grateful, thanks again. Regards David
From: Roger on 29 Jan 2010 19:00 On Jan 29, 3:50 pm, BobbyDazzler <david.a.mitch...(a)inbox.com> wrote: > Roger, you are officially my best friend forever! Just a couple of > questions, if thats ok? > > Why do you use line numbers in tblinvoicedetail? I would of thought > that the invdetailid autonumber would have been sufficient? > > This line doesn't work : - > > lnginvoiceid = !invoiceId > > it just adds the invoiceid of the first record in tblinvoices > everytime so i added a llittle code to get the max record which seems > to work fine. Is there maybe some sort of requery required after > the .update? > > From your code I was even able to amend it to allow me to add the > product to an existing record so you've helped kill 2 birds with one > stone! > > Very grateful, thanks again. > > Regards > > David lineNumber is useful if you want to sequence lines in particular order, on the invoice says you start with 2 detail lines, invoiceDetailId product 100 saw 101 hammer now says you want to add a third product for this invoice, 102 screwdriver but you want to print the products on the invoice as saw screwdriver hammer if you have a 'lineNum', you can resequence the list of products as you see fit
|
Pages: 1 Prev: access97, comboBox, long string Next: access97 comboBox filtering |