R
rebecky
Hello. I have an Employer Database with tables stored on SQL Server and
Access MDB user interface.
This Database is connected via ODBC connection at several locations.
My Switchboard form was set to open to a new record, DoCmd.GoToRecord , ,
acNewRec. There were no problems for months, and then all of sudden the
users were getting a visual basic error when they opened the form and this
line was highlighted. I cannot recall exactly what the error message said.
Anyway, can anyone tell me why this would suddenly go buggy and what I can do
to prevent it happening again?(other than removing the code to go to new
record)
Also, in this same database, I have a Main form for Employer Information with
a Subform for Job Order Information. In the Job Order Information subform, I
have 2 little subforms having to do with contact information per job. Some
jobs have more than one contact per job and some have only one. One subform
is for the contact at the job and the other subform is for the contact here
at our organization. The job contact info is stored in one table and the
contact info for here is in another table. Some Employers have several Jobs
and these Jobs may or may not have the same contact info...same with our
organization contact info.
I was trying to use Combo Boxes on the parent subform to find the information
in the contact subforms so that the user would not have to re-enter the
contact info when the job has the same contact info as another job for that
employer. I want them to be able to add new contact info and have it show up
in the subforms right away and I want them to be able to go to a new record
and add an additional contact for any one job via these subforms. I have a
NotInList event - I will show you here:
Dim Result
Dim Msg As String
Dim CR As String ' Carriage Return
'Title = "TED version 2.1"
CR = vbCrLf
' Return Control object that points to combo box.
Dim ctl As Control
Set ctl = Me![stsID] 'An alias for the name of this combo-box, whichreduces
typing.
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
NewData = StrConv(NewData, vbProperCase) ' Converts new entry to ProperCase.
' Ask the user if he or she wishes to add the new customer.
Msg = ""
Msg = Msg & "'" & NewData & "' is not in TED STS Contacts" & CR & CR
Msg = Msg & "Do you want to add this STS Contact?"
If MsgBox(Msg, vbQuestion + vbYesNo, "TED version 2.1") = vbYes Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
'** MUST ** undo entry first, or you get "Can't go to specified record"
ctl.Undo
Me![StsSubform].SetFocus
'[Form_StsSubform].[PositionID] = Me.PositionID
Me![StsSubform].Form.Recordset.AddNew
[Form_StsSubform].[stsContact] = NewData ' "Paste" in the new, correctly
formated name.
Me.StsSubform.Form.Dirty = True
'show subform that the record needs saving
DoCmd.RunCommand acCmdSaveRecord
'Me.StsSubform.Requery
'Supress error message
Response = acDataErrContinue
'Msg = "" 'Give the user further instructions, and an "out" if this isn't
what they wanted.
'Msg = Msg & "TED has Entered Employer Name. Please Continue " & CR
'Msg = Msg & "Entering Employer Information, or Use the Undo Button to
Cancel"
'MsgBox (Msg)
'doCmd.RunCommand acCmdSaveRecord
MsgBox "TED has Entered STS Contact Name Please Continue Entering STS
Contact Information or Use the Undo Button", vbOKOnly, "TED Version 2.1"
'DoCmd.GoToControl , , [Form_StsSubform].stsPhone
'ctl.Requery
Else
'User clicked the No button here.
'You could display a message to "try again", but that just annoys mostof
us.
'Supress error message
Response = acDataErrContinue
ctl.Undo
SendKeys "{ESC}"
Exit Sub
End If
Set ctl = Nothing ' ANY time you use "Set", you should "unset" it
beforeleaving.
'ErrContinue
Me.stsID.Requery
Nothing about this is working. They cannot go to "next" record and add an
additional contact using the combo box or it changes the data in the first
record they added. The data does not show up in the subform unless they go
away to another record and come back and then select it from the combo box.
Can anyone think of a better way to accomplish what I want to accomplish, or
tell me what I am doing wrong here?
Thank you so much
rebecky
Access MDB user interface.
This Database is connected via ODBC connection at several locations.
My Switchboard form was set to open to a new record, DoCmd.GoToRecord , ,
acNewRec. There were no problems for months, and then all of sudden the
users were getting a visual basic error when they opened the form and this
line was highlighted. I cannot recall exactly what the error message said.
Anyway, can anyone tell me why this would suddenly go buggy and what I can do
to prevent it happening again?(other than removing the code to go to new
record)
Also, in this same database, I have a Main form for Employer Information with
a Subform for Job Order Information. In the Job Order Information subform, I
have 2 little subforms having to do with contact information per job. Some
jobs have more than one contact per job and some have only one. One subform
is for the contact at the job and the other subform is for the contact here
at our organization. The job contact info is stored in one table and the
contact info for here is in another table. Some Employers have several Jobs
and these Jobs may or may not have the same contact info...same with our
organization contact info.
I was trying to use Combo Boxes on the parent subform to find the information
in the contact subforms so that the user would not have to re-enter the
contact info when the job has the same contact info as another job for that
employer. I want them to be able to add new contact info and have it show up
in the subforms right away and I want them to be able to go to a new record
and add an additional contact for any one job via these subforms. I have a
NotInList event - I will show you here:
Dim Result
Dim Msg As String
Dim CR As String ' Carriage Return
'Title = "TED version 2.1"
CR = vbCrLf
' Return Control object that points to combo box.
Dim ctl As Control
Set ctl = Me![stsID] 'An alias for the name of this combo-box, whichreduces
typing.
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
NewData = StrConv(NewData, vbProperCase) ' Converts new entry to ProperCase.
' Ask the user if he or she wishes to add the new customer.
Msg = ""
Msg = Msg & "'" & NewData & "' is not in TED STS Contacts" & CR & CR
Msg = Msg & "Do you want to add this STS Contact?"
If MsgBox(Msg, vbQuestion + vbYesNo, "TED version 2.1") = vbYes Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
'** MUST ** undo entry first, or you get "Can't go to specified record"
ctl.Undo
Me![StsSubform].SetFocus
'[Form_StsSubform].[PositionID] = Me.PositionID
Me![StsSubform].Form.Recordset.AddNew
[Form_StsSubform].[stsContact] = NewData ' "Paste" in the new, correctly
formated name.
Me.StsSubform.Form.Dirty = True
'show subform that the record needs saving
DoCmd.RunCommand acCmdSaveRecord
'Me.StsSubform.Requery
'Supress error message
Response = acDataErrContinue
'Msg = "" 'Give the user further instructions, and an "out" if this isn't
what they wanted.
'Msg = Msg & "TED has Entered Employer Name. Please Continue " & CR
'Msg = Msg & "Entering Employer Information, or Use the Undo Button to
Cancel"
'MsgBox (Msg)
'doCmd.RunCommand acCmdSaveRecord
MsgBox "TED has Entered STS Contact Name Please Continue Entering STS
Contact Information or Use the Undo Button", vbOKOnly, "TED Version 2.1"
'DoCmd.GoToControl , , [Form_StsSubform].stsPhone
'ctl.Requery
Else
'User clicked the No button here.
'You could display a message to "try again", but that just annoys mostof
us.
'Supress error message
Response = acDataErrContinue
ctl.Undo
SendKeys "{ESC}"
Exit Sub
End If
Set ctl = Nothing ' ANY time you use "Set", you should "unset" it
beforeleaving.
'ErrContinue
Me.stsID.Requery
Nothing about this is working. They cannot go to "next" record and add an
additional contact using the combo box or it changes the data in the first
record they added. The data does not show up in the subform unless they go
away to another record and come back and then select it from the combo box.
Can anyone think of a better way to accomplish what I want to accomplish, or
tell me what I am doing wrong here?
Thank you so much
rebecky