By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,509 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,509 IT Pros & Developers. It's quick & easy.

Unsure where my pointer goes in DAO?

P: n/a
MLH
Say I'm walking a subset of the records in tblAddnlOwners
via DAO. Suppose there are 5 records in the extract and that
I MoveFirst, MoveNext and MoveNext. Then,
when on the 3rd of 5 records, I determine the
need to append a record to tblAddnlOwners.
So, I use AddNew and do it.

Now, I'd like to keep on walking the records.
I wanna go to the 4th then the 5th and be done
with it all. As I'm walking, I might have to append
a new record to tblAddnlOwners at any (or all) of
the 5-steps along the way. I don't want things to
get out of order in the process. I've read the HELP.
I still think I need a little hand-holding here.

Here's part of the code I use ...
100 Dim MyDB As Database, qdfMarkedAddnlOwnerRecs As QueryDef,
rstMarkedAddnlOwners As Recordset
110 Dim PString2 As String, PLong As Long
120 Set MyDB = CurrentDb ' Set MyDB =
OpenDatabase("Northwind.mdb") is alternate syntax.
130 WaitTime = DLookup("[CertMailResponseWaitTime]", "tblAdmin")
140 With MyDB
149 Set qdfMarkedAddnlOwnerRecs = .CreateQueryDef("", "Select *
From tblAddnlOwners Where [AOActionMark]=True;")
150 Set rstMarkedAddnlOwners =
..OpenRecordset("qdfMarkedAddnlOwnerRecs", dbOpenSnapshot)
230 With rstMarkedAddnlOwners
240 .MoveFirst
250 Do Until rstMarkedAddnlOwners.EOF
260 PLong = !VehicleJobID
270 If IsNull(PLong) Then
280 .Edit
290 !VehicleJobID = CurrentVehicleJobID
300 .Update
310 Else
320 OtherColor = PLong
' SAY "BEN FIX IS ALREADY LISTED AS BEING THE
OWNER OF A 1979 CORVETTE ENTERED ON JULY 22, 1996.
' DID YOU INTEND TO MAKE BEN FIX AN ADDITIONAL
OWNER OF THE 2006 CHRYSLER YOU JUST ENTERED AS WELL?
330 PString = !AddnlOwnrFName & " " &
!AddnlOwnrLName & " is already listed as beingthe owner of a "
340 PString = PString & DLookup("[VDescr]",
"qryVehiclesNowners", "[VehicleJobID]=GetCurrentVehicleJobID()")
350 PString2 =
Trim$(CStr(DLookup("[VehicleJobTDstamp]", "tblVehicleJobs",
"[VehicleJobID]=GetCurrentVehicleJobID()")))
360 PString = PString & " entered on " & PString2
& ". Did you intend to make " & !AddnlOwnrFName & " " &
!AddnlOwnrLName
370 PString2 = Trim$(CStr(DLookup("[VDescr]",
"qryVehiclesNowners", "[VehicleJobID]=GetOtherColor()")))
380 PString = PString & " an additional owner of
the " & PString2 & " you just entered as well?"
390 PString = PString & ""
400 PString = PString & ""
410 Response = AskUserQ(PString, "Well, did you?",
vbQuestion, vbDefaultButton1)
' IF NO, DO NOTHING. IF ANSWER=YES, THEN APPEND
AN ADDITIONAL RECORD FOR BEN FIX TO tblAddnlOwnrs, SET ITS
[VehicleJobID] FIELD=CURRENTVEHICLEJOBID.
420 If Response = False Then '
430 PString = "As you wish. No additional
owners will be recorded for this vehicle. FYI: To enter additional "
440 PString = PString & "owners later on for
any vehicle already on file in Tow-Pak, click Data Management "
450 PString = PString & "on the main menu and
Button-R on the Administrative Activity form."
460 DoCmd.OpenForm "frmBigMsgBox", , , , ,
acDialog, PString
470 End If
' GATHER THE DATA AND APPEND A RECORD TO
TBLADDNLOWNERS USING SOMETHING LIKE THIS
' .AddNew
' !VehicleJobID
' !AddnlOwnrFName
' !AddnlOwnrLName
' !AddnlOwnrAddr
' !AddnlOwnrPObox
' !AddnlOwnrCity
' !AddnlOwnrState
' !AddnlOwnrZip
' !AddnlOwnrCounty
' !AddnlOwnrPhone
' !AddnlOwnrTaxID
' !UserID
' .Update
480 End If
490 Loop
500 .Close

What I want to be sure of is that I don't get stuff out
of order between lines 470 and 480. Sorry about the
length of these lines. You'll probably have to cut 'n
paste it into a wider frame to make sense of it.
Mar 24 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Without going through your code, just a quick comment that may help.

Open 2 recordsets, the 5 records you mention as a snapshot and the full
table as a dynaset. You would then go through the 5 records in the snapshot.
Since the snapshot isn't editable, you don't have to worry about the new
records you add affecting it. If you need to add records, add them to the
dynaset recordset that you also opened. It won't matter where in the table
they get added. Your 5 records in the snapshot that you're stepping through
will still be there, untouched. When you're ready to go to the next record
in the snapshot, just MoveNext (until EOF, of course).

--
Wayne Morgan
MS Access MVP
"MLH" <CR**@NorthState.net> wrote in message
news:ht********************************@4ax.com...
Say I'm walking a subset of the records in tblAddnlOwners
via DAO. Suppose there are 5 records in the extract and that
I MoveFirst, MoveNext and MoveNext. Then,
when on the 3rd of 5 records, I determine the
need to append a record to tblAddnlOwners.
So, I use AddNew and do it.

Now, I'd like to keep on walking the records.
I wanna go to the 4th then the 5th and be done
with it all. As I'm walking, I might have to append
a new record to tblAddnlOwners at any (or all) of
the 5-steps along the way. I don't want things to
get out of order in the process. I've read the HELP.
I still think I need a little hand-holding here.

Here's part of the code I use ...
100 Dim MyDB As Database, qdfMarkedAddnlOwnerRecs As QueryDef,
rstMarkedAddnlOwners As Recordset
110 Dim PString2 As String, PLong As Long
120 Set MyDB = CurrentDb ' Set MyDB =
OpenDatabase("Northwind.mdb") is alternate syntax.
130 WaitTime = DLookup("[CertMailResponseWaitTime]", "tblAdmin")
140 With MyDB
149 Set qdfMarkedAddnlOwnerRecs = .CreateQueryDef("", "Select *
From tblAddnlOwners Where [AOActionMark]=True;")
150 Set rstMarkedAddnlOwners =
.OpenRecordset("qdfMarkedAddnlOwnerRecs", dbOpenSnapshot)
230 With rstMarkedAddnlOwners
240 .MoveFirst
250 Do Until rstMarkedAddnlOwners.EOF
260 PLong = !VehicleJobID
270 If IsNull(PLong) Then
280 .Edit
290 !VehicleJobID = CurrentVehicleJobID
300 .Update
310 Else
320 OtherColor = PLong
' SAY "BEN FIX IS ALREADY LISTED AS BEING THE
OWNER OF A 1979 CORVETTE ENTERED ON JULY 22, 1996.
' DID YOU INTEND TO MAKE BEN FIX AN ADDITIONAL
OWNER OF THE 2006 CHRYSLER YOU JUST ENTERED AS WELL?
330 PString = !AddnlOwnrFName & " " &
!AddnlOwnrLName & " is already listed as beingthe owner of a "
340 PString = PString & DLookup("[VDescr]",
"qryVehiclesNowners", "[VehicleJobID]=GetCurrentVehicleJobID()")
350 PString2 =
Trim$(CStr(DLookup("[VehicleJobTDstamp]", "tblVehicleJobs",
"[VehicleJobID]=GetCurrentVehicleJobID()")))
360 PString = PString & " entered on " & PString2
& ". Did you intend to make " & !AddnlOwnrFName & " " &
!AddnlOwnrLName
370 PString2 = Trim$(CStr(DLookup("[VDescr]",
"qryVehiclesNowners", "[VehicleJobID]=GetOtherColor()")))
380 PString = PString & " an additional owner of
the " & PString2 & " you just entered as well?"
390 PString = PString & ""
400 PString = PString & ""
410 Response = AskUserQ(PString, "Well, did you?",
vbQuestion, vbDefaultButton1)
' IF NO, DO NOTHING. IF ANSWER=YES, THEN APPEND
AN ADDITIONAL RECORD FOR BEN FIX TO tblAddnlOwnrs, SET ITS
[VehicleJobID] FIELD=CURRENTVEHICLEJOBID.
420 If Response = False Then '
430 PString = "As you wish. No additional
owners will be recorded for this vehicle. FYI: To enter additional "
440 PString = PString & "owners later on for
any vehicle already on file in Tow-Pak, click Data Management "
450 PString = PString & "on the main menu and
Button-R on the Administrative Activity form."
460 DoCmd.OpenForm "frmBigMsgBox", , , , ,
acDialog, PString
470 End If
' GATHER THE DATA AND APPEND A RECORD TO
TBLADDNLOWNERS USING SOMETHING LIKE THIS
' .AddNew
' !VehicleJobID
' !AddnlOwnrFName
' !AddnlOwnrLName
' !AddnlOwnrAddr
' !AddnlOwnrPObox
' !AddnlOwnrCity
' !AddnlOwnrState
' !AddnlOwnrZip
' !AddnlOwnrCounty
' !AddnlOwnrPhone
' !AddnlOwnrTaxID
' !UserID
' .Update
480 End If
490 Loop
500 .Close

What I want to be sure of is that I don't get stuff out
of order between lines 470 and 480. Sorry about the
length of these lines. You'll probably have to cut 'n
paste it into a wider frame to make sense of it.

Mar 24 '06 #2

P: n/a
MLH
Thanks a bunch, Wayne. I'm sure that your comments
will help. I'll have a go at it.
Mar 24 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.