2 Switchboard Questions

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Q1: I have two forms based on a single table. One to
input new customer records that goes directly to a new
record and does not allow editing of existing records.
This form works great. The other form for updating
customer records and allows for editing of existing
records. I want to use a new form to pull up the entire
record based on user input of the customer ID.

I want the new form to have one field named CustID, and
two buttons. One for selecting the appropriate record
based on the CustID input (using a drop-down menu from the
original table, if possible), and one for canceling and
going back to the main switchboard.

Q2: Below is the code for one of my update forms. I have
set things up so that my main switchboard and other forms
all open maximized. How can I have it so that when the
update button is clicked, the main switchboard is closed
and the update form is maximized, and then when the update
form is closed, the main switchboard opens maximized?


Private Sub UpdateExistingCust_Click()
On Error GoTo Err_UpdateExistingCust_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

Exit_UpdateExistingCust_Click:
Exit Sub

Err_UpdateExistingCust_Click:
MsgBox Err.Description
Resume Exit_UpdateExistingCust_Click

End Sub
 
Q1: You could do a couple of things. Easiest would just be to use one form -
the one you've created for user input and call it different ways. For
instance upon opening you could pass it an argument to tell it if this is
supposed to be a new record or if this person is editing records:

From the switchboard:

DoCmd.OpenForm "Form_Name", acNormal, , , , , "Add"

On the form:

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs = "Add" Then
DoCmd.GoToRecord , , acNewRec
End If
End Sub

Then you could create a combo box control whose Row Source was a query of
the CustID field and the AfterUpdate Event could change to the proper record
(Don't set the ControlSource - leave it unbound):

Private Sub ComboBox1_AfterUpdate()
DoCmd.FindRecord Me!ComboBox1, acEntire, False, acSearchAll, False,
acCurrent, True
End Sub

Or you could use a second form as you mentioned and use the above code for
the AfterUpdate for the button to navigate to the proper record, or if you
only want them to be able to edit the specific record then:

Private Sub Button1_Click()
Dim stSql
stSql = "SELECT CustomersTable.* FROM CustomersTable WHERE CustID = " &
Me!ComboBox1 & ";
Me!RecordSource = stSql
End Sub

Q2: To use the automatic closing of other forms, try using the OnOpen Event.
As for the code to close the forms:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Close acForm, "Switchboard_Form_Name"
End Sub

Or add the code to existing button:

...DoCmd.OpenForm stDocName, , , stLinkCriteria
...DoCmd.Maximize
DoCmd.Close acForm, "Switchboard_Form_Name"

Good luck!
 
Back
Top