Preferred reading, writing and looping code

[Guest]

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

 

[Tim Ferguson]

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

 

[Guest]

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

 

[Tim Ferguson]

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