How to move through a recordset 2

[ruthcali]

I am using Access97.

I would like to open my form, go to each record, one by one, and run some code.

i have tried:

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("SELECT tblCircuit.* FROM tblCircuit&quot

Do Until rec.EOF
Call Sub1
rec.MoveNext
Loop
rec.Close

But, it's not working.
Thanks,
Ruth ruth.jonkman@wcom.com

 

[MichaelRed]

but Ruth,

... what does it Do?

Where is the Refresh action for the form?

What do you EXPECT it to do?

How are you supposed tjnow what (if anything) it did?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Rohdem]

Do you get an error message? If so, what line is it on?
What does 'sub1' do? Do you need to pass it any values to work with? Mike Rohde
rohdem@marshallengines.com

 

[ruthcali]

Hi,

I have an Excel sheet with two columns:
projectName and CircuitID.

Circuit ID is in the format:
13-Location1-Location2-001

In Access, i made a table with these columns:
projectName, CircuitID, Loc1,Loc2,State1,State2

The first two columns (projectName and CircuitID) will be filled in from my Excel data.

The other 4 columns can be calculated.

i created a form (frmCircuits) with a sub called Sub1 to fill in the other 4 values:

Dim Dash As String
Dim findDash1
Dim findDash2

Dash = "-"

'this looks for the first dash in the string Circuit. it returns the position of
'that number to the variable called FindDash1. that is a way to find the first location.

findDash1 = InStr(1, Circuit, Dash, vbTextCompare)

'this looks for the second dash in the string Circuit. it returns the position of
'that number to the variable called FindDash2. that is a way to find the second location.

findDash2 = InStr(findDash1 + 1, Circuit, Dash, vbTextCompare)

Me.Loc1 = Trim(Mid(Circuit, findDash1 + 1, 3))
Me.Loc2 = Trim(Mid(Circuit, findDash2 + 1, 3))


'uses the Loc1 and Loc2 fields to look in the table
'tblAddress to find the State.

Me.State1 = DLookup("State", "tblAddress", "[Site Code] Like " & "'" & Forms![frmCircuits]![Loc1] & "*" & "'&quot

Me.State2 = DLookup("State", "tblAddress", "[Site Code] Like " & "'" & Forms![frmCircuits]![Loc2] & "*" & "'&quot
End Sub


At the moment, i have the Form_Current and Circuit_AfterUpdate calling this function.

Everything works fine, but you have to click with the mouse on the specific record for the Loc1, Loc2, State1 and State2 fields to get filled in.

Because otherwise, the on_current event isn't getting triggered.

Am i doing this the wrong way?
thanks,
ruth ruth.jonkman@wcom.com

 

[ruthcali]

If you don't want to read my long text above, i guess an easier question would be:

how can i automatically trigger the OnCurrent event to happen for every record on my form? ruth.jonkman@wcom.com

 

[Rohdem]

Instead of trying to store your calculated values in the table, how about calculating them on the form. Set the control source of the text box to equal your calculation, i.e.

control source for loc1:
= Trim(Mid(Circuit, findDash1 + 1, 3))

control source for loc2:
= Trim(Mid(Circuit, findDash2 + 1, 3))

control source for state1:
= DLookup("State", "tblAddress", "[Site Code] Like " & "'" & Forms![frmCircuits]![Loc1] & "*" & "'&quot

control source for state2:
= DLookup("State", "tblAddress", "[Site Code] Like " & "'" & Forms![frmCircuits]![Loc2] & "*" & "'

This will calculate the value on a record by record basis with no need to click on each record.
Mike Rohde
rohdem@marshallengines.com

 

[MichaelRed]

Ruth,

I have looked at your explination. I don't think you need a form at all. If this is (as it appears to be) a "One-Shot" process which simply transfers the spreadsheet to a tble in Ms. Access, A brief module and a "Make Table" query should be suffficient.

If you do not 'interact' with the process (e.g. manually enter/edit any of the records in the process), a form would only be 'whizzing by' if you did use it.

e-mail me a copy of you [tblAddress], and I'll look at it briefly on Wed. I don't hink it should take more than ~ 1/2 hour to get the procedure & query done. Meanwhile, you could be looking fro a new job for me.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ruthcali]

Mike Rohde,

I tried your suggestion above, but i keep getting #Name in my loc1 and loc2 text boxes and #Error in my state1 and state2 text boxes.

In the table, i deleted the loc1,loc2,state1 and state2 fields.

then, i set the control sources like you mentioned above.

i still need the following code though:

Dim Dash As String
Dim FindDash1
Dim findDash2

Dash = "-"

FindDash1=InStr(1, Circuit, Dash, vbTextCompare)
findDash2=InStr(FindDash1 + 1, Circuit, Dash, vbTextCompare)

so i have the code triggered by the FormLoad event.

Am i missing something?
Thanks,
Ruth
ruth.jonkman@wcom.com

 

[ruthcali]

I found that if i put the code in my report, it works fine!

I took out the 4 fields from my table (loc1, loc2, state1, state2) and do the calculations in my report.


For the form, Mike Rohde emailed me a sample using his suggestion above that works by calculating the values in each of the unbound text boxes.

if anyone is interested, i can email it. ruth.jonkman@wcom.com