Very new to VBA, could use some basic help

[oregoncrete]

Hello all,



I am quite new to VBA, but am learning quickly. I am trying to eventually find which day we hit our breakpoint, then post that date to a table, and any remaining sales be tallied up into a Sales after Breakpoint field. Right now I am just working on creating an equation to find our breakpoint.

Here is what I have so far:

Public Sub BreakPoint()
Dim cum_sales As Currency
Dim bp_found As String
Dim prof_postbp As Currency
Dim FindBP As Recordset
Set FindBP = CurrentDb.OpenRecordset("Totals By Date", dbOpenDynaset)
cum_sales = 0
bp_found = n
If Not FindBP.EOF Then FindBP.MoveFirst
If bp_found = n Then
Do While cum_sales - [Module Cost] < 0
FindBP.MoveNext
cum_sales = cum_sales + [Total Sales]
Loop
 
End If
End Sub

I do not think I am referencing records correctly and would like some help with this. I want to be using my current database, and I want the table it is accessing to be Totals By Date.

Any help is greatly appreciated!

[pbaldy]

It would be

cum_sales = cum_sales + FindBP![Total Sales]

[ajetrumpet]

george,

two things. First, the rs needs to be opened officially before you start moving between the recs:

FindBP.movelast
FindBP.movefirst

And to reference a field in a recordset, the ! symbol is used:

FindBP!fieldnameHere

And, if the field name has spaces in it, you need [] braces to combat the parsing issue:

FindBP![field name here]

[pbaldy]

george,

two things. First, the rs needs to be opened officially before you start moving between the recs:

FindBP.movelast
FindBP.movefirst

In my experience that's only necessary when you want an accurate record count. I never do it for loops like that.

I'd add that this line is unnecessary:

If Not FindBP.EOF Then FindBP.MoveFirst

The recordset will always start at the first record.

[oregoncrete]

Thank you both greatly for the help. I have made those changes and I have receive this error:

Runtime Error '3265':

Item not found in this collection.

When Debugged it highlights this portion:

cum_sales = cum_sales + FindBP![Total Sales]

Any advice?



EDIT*

In my experience that's only necessary when you want an accurate record count. I never do it for loops like that.

I'd add that this line is unnecessary:

If Not FindBP.EOF Then FindBP.MoveFirst

The recordset will always start at the first record.

This is great help also, thank you!

[pbaldy]

The error implies that isn't the actual field name in the table. Is it perhaps an underscore rather than a space?

[oregoncrete]

The error implies that isn't the actual field name in the table. Is it perhaps an underscore rather than a space?

You're a life-saver; it was an underscore. Thank you so very much!

[pbaldy]

We were happy to help!

[oregoncrete]

Actually, I could use a little more help now. That is if anyone is up to it . Once it has found cum_sales >= 0 I need it to post the Sales Date from this record to a Breakeven Date field on another table.

Would this look like:

If cum_sales - FindBP![Module Cost] >= 0 Then
bp_found = y
If bp_found = y Then
_______ FindBP![Sales Date] to Seasons![Breakeven Date]

I dont know which action would go before FindBP![Sales Date]

[ajetrumpet]

In my experience that's only necessary when you want an accurate record count. I never do it for loops like that.

I'd add that this line is unnecessary:

If Not FindBP.EOF Then FindBP.MoveFirst

The recordset will always start at the first record.

thanks, Paul. I will send you a message if I prove that wrong. I have never actually tested it. Just always assumed the safe side.

[pbaldy]

Oregon: You can open a recordset on the other table, then

rsOtherTable!FieldName = FindBP![Sales Date]

Adam: Just checked in the db I'm working on. I open 25 recordsets throughout the db, and there are only 2 "MoveLast" lines, both of which when I needed a record count. If it's required in some instance other than that, I haven't run into it.

[oregoncrete]

Oregon: You can open a recordset on the other table, then

rsOtherTable!FieldName = FindBP![Sales Date]

Adam: Just checked in the db I'm working on. I open 25 recordsets throughout the db, and there are only 2 "MoveLast" lines, both of which when I needed a record count. If it's required in some instance other than that, I haven't run into it.

Thank you for your continued help, I am sure this is what I will need, however, how do I open a second recordset simultaneous to my first?

If I do:

Set FindBP = CurrentDb.OpenRecordset("OTHER TABLE", dbOpenDynaset)
then won't FindBP![Sales Date] give me an error since it was on my original opened recordset?

[pbaldy]

Along the lines of:

Dim FindBP As Recordset
Dim rsTarget As Recordset

Set FindBP = CurrentDb.OpenRecordset("Totals By Date", dbOpenDynaset)
Set rsTarget = CurrentDb.OpenRecordset("TheOtherTable", dbOpenDynaset)

That said, I'm not sure where you want the value in the other table. If a new record then that will work and you'd use .AddNew. If you mean to modify an existing record, you'd want to open the recordset on an SQL statement that only returned the desired record, and use .Edit.

[oregoncrete]

Along the lines of:

Dim FindBP As Recordset
Dim rsTarget As Recordset

Set FindBP = CurrentDb.OpenRecordset("Totals By Date", dbOpenDynaset)
Set rsTarget = CurrentDb.OpenRecordset("TheOtherTable", dbOpenDynaset)

That said, I'm not sure where you want the value in the other table. If a new record then that will work and you'd use .AddNew. If you mean to modify an existing record, you'd want to open the recordset on an SQL statement that only returned the desired record, and use .Edit.

Thank you! Unfortunately at this time I do not know any SQL, but I am doing my best to learn quickly.

The table that contains my sales data has a new record for each product set on every day. So say Set 1 sells 10$ on day 1 and so on. So on this table there are numerous records for Set 1. On the other table I have everything categorized by timeframe, so say in Winter '10 we had Set 1 and Set 2.

EX:

Table 1:
(Set 1) (###$) (1/1/11) (Set 1 Cost)
(Set 1) (###$) (1/2/11) (Set 1 Cost)
(Etc.)

Table 2:
(Winter '10) (Set 1) (Set 1 Cost) (Breakpoint Date)

I have it calculating Sales for Set 1 on day one, then adding cumulatively. So cumulative sales - Set Cost >= 0 then update breakpoint date on Table 2.

Again, thank you very much. If this is too time-consuming or I am asking too much then please don't hesitate to tell me so