Setting focus from switchboard when user select add mode

[Guest]

Please help. I have a switchboard and a form. The form calls the recordset
values that match VendorID from the table; based on a combobox. However,
when I want to enter a new item, in this case Vendor, the code does not seem
to find the .eof statement and add a new ID or allow adding a new Vendor. I
have been directed to pursue a solution using the switchboard feature,
add/edit. However, the form code sets focus on the combo box and to add a
new item would require setting focus on a text box, txtVendor, as well as
going to eof and creating a new VendorID. I do not have the knowledge nor
can I see where to add the code. If someone could help me, I would be very
grateful. Thank you in advance

 

[Guest]

Post your code.

 

[Guest]

Private Sub Form_Load()
Me!cboVendorID.SetFocus
End Sub

Private Sub cboVendorID_AfterUpdate()
Dim objVendor As Vendor

If Not IsNull(Me!cboVendorID) Then
' Enable the edit controls,
' and disable the Save button.
Call EnableControls(Enable:=True)
Call EnableSave(Enable:=False)

Set objVendor = New Vendor
' Set the new Vendor ID property, so you can
' use the Load method.
objVendor.ID = Me!cboVendorID
If objVendor.Load() Then
' Display the loaded fields on the form,
' and then select the Vendor field.
Call ScatterFields(objVendor)
Me!txtVendor.SetFocus
End If
End If
End Sub

Public Function Load() As Boolean
Dim rst As ADODB.Recordset

On Error GoTo HandleErrors

Set rst = New ADODB.Recordset
rst.Open "Select * from tblVendor WHERE VendorID = " & Me.ID, _
Application.CurrentProject.Connection
With rst
If Not .EOF Then
mstrVendor = !Vendor
mstrPOC = !POC
mstrPhone = !Phone
mstrFax = !Fax
mstrAddress = !Address
mstrCity = !City
mstrZip = !Zip
mstrRemarks = !Remarks
mlngStateID = !StateID
mlngVendorID = !VendorID

End If
End With
Load = True

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Exit Function

HandleErrors:
Load = False
Resume ExitHere
End Function

Public Function Save() As Boolean
Dim rst As ADODB.Recordset

On Error GoTo HandleErrors

Set rst = New ADODB.Recordset
rst.Open "Select * from tblVendor WHERE VendorID = " & Me.ID, _
Application.CurrentProject.Connection, adOpenKeyset, _
adLockPessimistic

With rst
If .EOF Then
.AddNew
' Set the ID property for this new Vendor.
Me.ID = !VendorID
End If
!Vendor = mstrVendor
!POC = mstrPOC
!Phone = mstrPhone
!Fax = mstrFax
!Address = mstrAddress
!City = mstrCity
!Zip = mstrZip
!Remarks = mstrRemarks
!StateID = mlngStateID
.Update
End With
Save = True

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Exit Function

HandleErrors:
Save = False
Resume ExitHere
End Function

There is much more code, should I post it all? I have no evidence that the
code ever reaches this last section.

 

[Guest]

hi again,
guessing but
in the function load(), what happen if it does not find a
vendor and is EOF? seems like that is where you should
have and else. something if EOF and somthing else if not
EOF I think that would be where i would try to set focus
on the vendor text box.

 

[Guest]

This code has been modified from one of the access developer books. In the
original form, as with this form's combo box, it is seeking a numerical
value. However, when it does not find the value, I do not believe the code
ever gets to the "public function save as boolean" where the following code
is listed:
If .EOF Then
.AddNew
' Set the ID property for this new Vendor.
Me.ID = !VendorID
End If

My supervisor does not want the users to be able to select the ID numbers,
so they are hidden.