Move a subform to the last record

G

Guest

I have a subform that is viewed in "Single form view"
When the user pushes the NewRecord button, it creates a new record based on
some information on the main form.

For some reason, I can't get it to display the record that it just created.
The navigation effectively now says record 1 of 2... but but it's sitting on
the first record, I'd like it to be on record 2 of 2
In otherwords, I'd like the form to be displaying the new record I just
created.

I have also provided the 'CreateNewOffice' routine which is inside the
NewRecord routine - although I feel it is irrelavent.

This is the code that I run for the NewRecord button

Sub NewRecord
Dim NewOffice As Long
Dim strFind As String

NewOffice = CreateNewOffice


'On Error Resume Next


MsgBox "A new office has been created"
SendKeys "^{F9}"

Me.RecordsetClone.FindFirst "[OfficeID] = " & NewOffice
Me.Bookmark = Me.RecordsetClone.Bookmark

end sub

'================================================
Public Function CreateNewOffice() As Long
Dim db As DAO.Database
Dim rsOffice As DAO.Recordset
Dim rsOfficeAddress As DAO.Recordset
Dim currentDocID As Long
Dim NewOfficeID As Long

Set db = CurrentDb
Set rsOffice = db.OpenRecordset("Offices")
Set rsOfficeAddress = db.OpenRecordset("OfficeAddresses")

'To Link the currentDocID to office and office addresses


currentDocID = Form_DocEntry.DocID

'MsgBox currentDocID



'1 need to take in to consideration what the provider type is
'that is now the old 'denoted by' field

'1 is person (Group Practice)
'2 is group (Practice)
'3 is Individual Provider

Select Case Form_DocEntry.DenotedBy
Case 1 'person (Group Practice)
'there is no PERSONAL billing information for a group practice
'a person will always have separate billing information for each
'office so there is no PERSONAL billing information that may serve
'as location information

With rsOffice
.AddNew
!DocID = currentDocID
!ProviderType = 5
.Update
.Bookmark = rsOffice.LastModified
End With



Case 2 'Practice
'Billing information for a practice (company, facility etc.)
'is it's own billing information
'For each new office, a pertinent question is whether or not
'to use it's billing address as the location address

With rsOffice
.AddNew
!DocID = currentDocID
!ProviderType = 2
!MVDocID = Val(Form_DocEntry.Doctors_FedIDNo)
.Update
.Bookmark = rsOffice.LastModified
End With

NewOfficeID = rsOffice!OfficeID

If MsgBox("Use this provider's billing address as the location
address for this office?", vbYesNo + vbQuestion, "Use Billing address for
this location?") = vbYes Then

With rsOfficeAddress
.AddNew
!OfficeID = NewOfficeID
!AddressTypeID = 1
If Not IsNull(Form_DocEntry.Street1) Then !StreetLine1 =
Form_DocEntry.Street1
If Not IsNull(Form_DocEntry.Street2) Then !StreetLine2 =
Form_DocEntry.Street2
If Not IsNull(Form_DocEntry.City) Then !City =
Form_DocEntry.City
If Not IsNull(Form_DocEntry.State) Then !State =
Form_DocEntry.State
If Not IsNull(Form_DocEntry.Zip) Then !Zip =
Form_DocEntry.Zip
.Update
.Bookmark = rsOfficeAddress.LastModified
End With


'make the office island field match the zip they typed in the
'address
Call ZipIslandMatch(NewOfficeID, Form_DocEntry.Zip)



End If




Case 3 'Individual Provider
'An independent provider has their own billing information -
'as well as separate billing information if they work under
'anyone in another office...
'It is also a pertinent question whether or not to use the
'billing address as the location address

With rsOffice
.AddNew
!DocID = currentDocID
!ProviderType = 1


If MsgBox("Does this office bill for the Provider's
individual practice?", vbYesNo + vbQuestion, "Independent Provider's
Office?") = vbYes Then
!MVDocID = Form_DocEntry.Doctors_FedIDNo
End If

.Update
.Bookmark = rsOffice.LastModified
End With

NewOfficeID = rsOffice!OfficeID

MsgBox "NewOfficeID is: " & NewOfficeID

If MsgBox("Use this provider's billing address as the location
address for this office?", vbYesNo + vbQuestion, "Use Billing address for
this location?") = vbYes Then


MsgBox "the OfficeID it's gonna use is: " & NewOfficeID


With rsOfficeAddress
.AddNew
!OfficeID = NewOfficeID
!AddressTypeID = 1
If Not IsNull(Form_DocEntry.Street1) Then !StreetLine1 =
Form_DocEntry.Street1
If Not IsNull(Form_DocEntry.Street2) Then !StreetLine2 =
Form_DocEntry.Street2
If Not IsNull(Form_DocEntry.City) Then !City =
Form_DocEntry.City
If Not IsNull(Form_DocEntry.State) Then !State =
Form_DocEntry.State
If Not IsNull(Form_DocEntry.Zip) Then !Zip =
Form_DocEntry.Zip
.Update
.Bookmark = rsOfficeAddress.LastModified
End With

'make the office island field match the zip they typed in the
'address
Call ZipIslandMatch(NewOfficeID, Form_DocEntry.Zip)



End If

End Select


rsOffice.Close
rsOfficeAddress.Close

CreateNewOffice = NewOfficeID

If Form_DocEntry.OfficesSub.Visible = False Then
Form_DocEntry.OfficesSub.Visible = True
End If
end function
 
R

Rob Oldfield

I can't figure out exactly what it is that you're trying to do... but I'd
guess that the recordset manipulation is overcomplicated. The SendKeys
statement is also bad... you should never really need them and they're
flaky.

I'd suggest posting another thread with details of exactly what it is that
you're trying to achieve (a new one, not another message in this one as then
you're probably limiting it to just me to answer). Maybe include a
reference back to this thread, but outline the actual issues you've come
across. There will be an easier way around it.


jonefer said:
I have a subform that is viewed in "Single form view"
When the user pushes the NewRecord button, it creates a new record based on
some information on the main form.

For some reason, I can't get it to display the record that it just created.
The navigation effectively now says record 1 of 2... but but it's sitting on
the first record, I'd like it to be on record 2 of 2
In otherwords, I'd like the form to be displaying the new record I just
created.

I have also provided the 'CreateNewOffice' routine which is inside the
NewRecord routine - although I feel it is irrelavent.

This is the code that I run for the NewRecord button

Sub NewRecord
Dim NewOffice As Long
Dim strFind As String

NewOffice = CreateNewOffice


'On Error Resume Next


MsgBox "A new office has been created"
SendKeys "^{F9}"

Me.RecordsetClone.FindFirst "[OfficeID] = " & NewOffice
Me.Bookmark = Me.RecordsetClone.Bookmark

end sub

'================================================
Public Function CreateNewOffice() As Long
Dim db As DAO.Database
Dim rsOffice As DAO.Recordset
Dim rsOfficeAddress As DAO.Recordset
Dim currentDocID As Long
Dim NewOfficeID As Long

Set db = CurrentDb
Set rsOffice = db.OpenRecordset("Offices")
Set rsOfficeAddress = db.OpenRecordset("OfficeAddresses")

'To Link the currentDocID to office and office addresses


currentDocID = Form_DocEntry.DocID

'MsgBox currentDocID



'1 need to take in to consideration what the provider type is
'that is now the old 'denoted by' field

'1 is person (Group Practice)
'2 is group (Practice)
'3 is Individual Provider

Select Case Form_DocEntry.DenotedBy
Case 1 'person (Group Practice)
'there is no PERSONAL billing information for a group practice
'a person will always have separate billing information for each
'office so there is no PERSONAL billing information that may serve
'as location information

With rsOffice
.AddNew
!DocID = currentDocID
!ProviderType = 5
.Update
.Bookmark = rsOffice.LastModified
End With



Case 2 'Practice
'Billing information for a practice (company, facility etc.)
'is it's own billing information
'For each new office, a pertinent question is whether or not
'to use it's billing address as the location address

With rsOffice
.AddNew
!DocID = currentDocID
!ProviderType = 2
!MVDocID = Val(Form_DocEntry.Doctors_FedIDNo)
.Update
.Bookmark = rsOffice.LastModified
End With

NewOfficeID = rsOffice!OfficeID

If MsgBox("Use this provider's billing address as the location
address for this office?", vbYesNo + vbQuestion, "Use Billing address for
this location?") = vbYes Then

With rsOfficeAddress
.AddNew
!OfficeID = NewOfficeID
!AddressTypeID = 1
If Not IsNull(Form_DocEntry.Street1) Then !StreetLine1 =
Form_DocEntry.Street1
If Not IsNull(Form_DocEntry.Street2) Then !StreetLine2 =
Form_DocEntry.Street2
If Not IsNull(Form_DocEntry.City) Then !City =
Form_DocEntry.City
If Not IsNull(Form_DocEntry.State) Then !State =
Form_DocEntry.State
If Not IsNull(Form_DocEntry.Zip) Then !Zip =
Form_DocEntry.Zip
.Update
.Bookmark = rsOfficeAddress.LastModified
End With


'make the office island field match the zip they typed in the
'address
Call ZipIslandMatch(NewOfficeID, Form_DocEntry.Zip)



End If




Case 3 'Individual Provider
'An independent provider has their own billing information -
'as well as separate billing information if they work under
'anyone in another office...
'It is also a pertinent question whether or not to use the
'billing address as the location address

With rsOffice
.AddNew
!DocID = currentDocID
!ProviderType = 1


If MsgBox("Does this office bill for the Provider's
individual practice?", vbYesNo + vbQuestion, "Independent Provider's
Office?") = vbYes Then
!MVDocID = Form_DocEntry.Doctors_FedIDNo
End If

.Update
.Bookmark = rsOffice.LastModified
End With

NewOfficeID = rsOffice!OfficeID

MsgBox "NewOfficeID is: " & NewOfficeID

If MsgBox("Use this provider's billing address as the location
address for this office?", vbYesNo + vbQuestion, "Use Billing address for
this location?") = vbYes Then


MsgBox "the OfficeID it's gonna use is: " & NewOfficeID


With rsOfficeAddress
.AddNew
!OfficeID = NewOfficeID
!AddressTypeID = 1
If Not IsNull(Form_DocEntry.Street1) Then !StreetLine1 =
Form_DocEntry.Street1
If Not IsNull(Form_DocEntry.Street2) Then !StreetLine2 =
Form_DocEntry.Street2
If Not IsNull(Form_DocEntry.City) Then !City =
Form_DocEntry.City
If Not IsNull(Form_DocEntry.State) Then !State =
Form_DocEntry.State
If Not IsNull(Form_DocEntry.Zip) Then !Zip =
Form_DocEntry.Zip
.Update
.Bookmark = rsOfficeAddress.LastModified
End With

'make the office island field match the zip they typed in the
'address
Call ZipIslandMatch(NewOfficeID, Form_DocEntry.Zip)



End If

End Select


rsOffice.Close
rsOfficeAddress.Close

CreateNewOffice = NewOfficeID

If Form_DocEntry.OfficesSub.Visible = False Then
Form_DocEntry.OfficesSub.Visible = True
End If
end function
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

moving to a record that was just created 1
What is "the Access way" to delete a record from a form? 4

Top