 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / May 2005Preferred reading, writing and looping code
I have remedial VBA skills and I think I could limp my way thru this cutting and pasting some old code but I would like to know the "preferred" way to accomplish this. I've got MyForm with (2) unbound text boxes plus I have (2) tables; JobTable and AmtTable. From a button on another existing form, I want to: Open Myform From JobTable get the first JobNum and load it into a text box on MyForm Enter a corresponding amount into the second text box Click an "OK" button on MyForm Write the first JobNum and the entered amount to AmtTable Then loop and do it again for the second and subsequent JobNums. This is a condensed version of what I need to accomplish. Any help with this code will be appreciated. Thanks, Marc =?Utf-8?B?TWFyYyBT?= <MarcS@discussions.microsoft.com> wrote in news:EB242240-FA5D-41FF-9B1D-5E74BCB4EC34@microsoft.com: Loads of assumptions taken here:.. > I've got MyForm with (2) unbound text boxes plus I have (2) tables; > JobTable and AmtTable. From a button on another existing form, I want > to: > > Open Myform Private sub cmdSomeButton_Click docmd.open "FMyForm", etc End Sub > From JobTable get the first JobNum and load it into a text box on > MyForm Enter a corresponding amount into the second text box ' (obviously) on the FMyForm form ... Private Sub Form_Open ' set the jobnum me.txtJobNum = GetFirstJobNum() ' get the amount and put it in the amount text box me.txtAmount = DLookUp("Amount", "OtherTable", _ "JobNum = " & txtJobNum) End Sub > Click an "OK" button on MyForm > Write the first JobNum and the entered amount to AmtTable Private Sub cmdWriteOut_Click dim strSQL as string ' should this really be an update rather than insert? strSQL = "INSERT INTO AmtTable (JobNum, Amount) " & _ "(" & txtJobNum & ", " & txtAmount & ")" CurrentDB.Execute strSQL, dbFailOnError End Sub > Then loop and do it again for the second and subsequent JobNums. What second and subsequent? Where does the user come in? Could this just be a single SQL update statement? > This is a condensed version of what I need to accomplish. Perhaps it would help to know exactly what you are trying to do... B Wishes Tim F Tim, Thanks for the reply - To provide more detail, I have a custom timekeeping program in VB (not written by me) that stores details of employees time in an mdb table. I have generated various queries and reports over the past few years with this data and have even done some VBA programming behind a form. However, I only do such programming once a year at best so it is like learning all over again each time I sit down to do it. At this time, I am working on creating invoices. I have a query that looks at the timesheet data and filters jobs that actually had charges against yhem in the past week. From a complete active job list of over 200, we may only work on 60 in one week. This filtered info is the JobTable - so for each JobNum in the JobTable, I want to generate an invoice which must have a unique number. Assume in JobTable, I have JobNum, Hours, Charges as the query result. The Invoice Table (which I referred to as AmtTable) is a new table I am generating to keep the summary Invoice information for posterity and to export in our accounting program. What I want to do is using an existing form I have created for various labor reports, create an Invoice Button which will loop thru the JobTable and for each job generate a new record in the AmtTable that includes a new invoice number and data entered by the user. I think to accomplish this, I want to open another form which popu;lates with the JobNum and allow the user enter the invoice amount (which for this type of invoice will be a fixed fee). Once the invoice amount is entered, I would think an "OK" button would be clicked to generate a new record in the AmtTable which would automatically fill in the key with a new invoice number and then the JobNum and InvoiceAmount would be updated from the new form. Once this is done, the routine would loop back to the next JobNum in JobTable and populate the new form. The user would enter the InvoiceAmount, click okay and the next JobNum would appear (with the previous information written to the AmtTable). There are a few more fields I will include; however, once I have the code to do one, I'm golden. As I mentioned, I have done somthing similar before and could probably cobble my way thru it. But I would like to know how the "pros" do something like this. Thanks again, Marc > =?Utf-8?B?TWFyYyBT?= <MarcS@discussions.microsoft.com> wrote in > news:EB242240-FA5D-41FF-9B1D-5E74BCB4EC34@microsoft.com: [quoted text clipped - 50 lines] > > Tim F =?Utf-8?B?TWFyYyBT?= <MarcS@discussions.microsoft.com> wrote in news:8F0EE786-D03E-4196-A027-1DFA09B7D7B9@microsoft.com: Hello again Marc > I have a query that looks at the timesheet data and filters jobs that > actually had charges against yhem in the past week. From a complete > active job list of over 200, we may only work on 60 in one week. This > filtered info is the JobTable If this stuff is generated in a query, what is the JobTable for? It'll be different the following week; but if the time sheet records are still there you can always recreate it at a moment's notice. Once you have any one fact recorded twice, it's only a matter of time before it disagrees with itself. > The Invoice Table (which I referred to as AmtTable) If it's a table full of invoices, why can't you just call it Invoices? > I want to open another form which popu;lates with > the JobNum and allow the user enter the invoice amount (which for this > type of invoice will be a fixed fee). Once the invoice amount is > entered, I would think an "OK" button would be clicked to generate a > new record in the AmtTable As I understand it, this AmtTable will include some columns that are actually derived from other tables (i.e. time sheets) and some arbitrary data that the user types in. How does the user know what to type in? If it's a "fixed amount" for all jobs this week, then it would seem to be a good thing for that to entered once with an UPDATE query on all this week's invoice records. If it's a fixed amount that depends on the JobNum, then it it should be kept in the Jobs table (the proper one, that models all the 2000 current jobs), and in that case you probably don't want it in the Invoices table at all, unless it's likely to change over the evolution of a job. > with a new invoice number Where does the invoice number come from? Does this have to fit with the accounting program, or are you free to create your own identifer like Format(InvoiceDate, "yyyyww") & JobNum or something like that? > The user would enter the InvoiceAmount, click okay and the next JobNum > would appear (with the previous information written to the AmtTable). That sounds like a pretty miserable job for some 60 records; and I would imagine pretty error prone too. An alternative, if the data really are arbitrary, would be a form in datasheet mode, so that records are more visible and easy to correct. > But I would like to know how the "pros" do > something like this. As you will have detected by now, I am very far from a "pro" in finance and management databases -- it seems to me that this sort of stuff should be done by commercial software where someone else takes all the risk -- but it strikes me that anything that involves copying loads of data that already exist somewhere ese is probably wrong... All the best Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.