[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with 2007 Access Template Combo Box Record Search

Posted on 2011-04-27
6
Medium Priority
?
1,153 Views
Last Modified: 2013-11-27
The following MS Office 2007 Access Template <Faculty> link:

office.microsoft.com/en-us/templates/faculty-TC001225346.aspx

...has a nice record search displaying the selected record in a textbox (actually this is pretty generic in most of their 2007 templates).  I've been trying to incorporate this into my form but I want nothing to do with embeded macros.  Stick with VBA.
Can someone please provide the After Update and On Got Focus for their cboGoToContact combo so it will show in the textbox exactly as in the Faculty Details Form on the Template using event procedures instead of the macros.  .  
0
Comment
Question by:zpotok
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35480237
you can convert those macros to VBA codes

open form in design view,
from ribbon
        Database tools > Macro
             Convert from's macro to Visual Basic
0
 

Author Comment

by:zpotok
ID: 35485044

Where is Database Tools > Macro? I'm using Access 2010


 Database Tools Macro
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35485547
for Access 2010, click Design, it will be at the Right  most part of the ribbon, but
as we have discussed before, this is a known bug in A2010.

wait till i get home and i will convert it using A2007 and post the VBA codes here.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35488182
here are the converted macros

 
'------------------------------------------------------------
' Form_Load
'
'------------------------------------------------------------
Private Sub Form_Load()
On Error GoTo Form_Load_Err

    If (IsNull(OpenArgs)) Then
        Exit Sub
    End If
    DoCmd.GoToRecord , "", acNewRec
    If (Not CurrentProject.IsTrusted) Then
        Exit Sub
    End If
    TempVars.Add "NewData", "Mid(Nz([OpenArgs]),InStr(Nz([OpenArgs]),""="")+1)"
    TempVars.Add "Space", "InStrRev([TempVars]![NewData],"" "")"
    On Error Resume Next
    If (TempVars!Space = 0 And TempVars!NewData <> "") Then
        [Last Name] = TempVars!NewData
    End If
    If (TempVars!Space > 0) Then
        [First Name] = Left(TempVars!NewData, TempVars!Space - 1)
        [Last Name] = Mid(TempVars!NewData, TempVars!Space + 1)
    End If
    TempVars.Remove "NewData"
    TempVars.Remove "Space"


Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit

End Sub


'------------------------------------------------------------
' cboGoToContact_AfterUpdate
'
'------------------------------------------------------------
Private Sub cboGoToContact_AfterUpdate()
On Error GoTo cboGoToContact_AfterUpdate_Err

    If (IsNull(Screen.ActiveControl)) Then
        Exit Sub
    End If
    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    TempVars.Add "ActiveControlValue", "[Screen].[ActiveControl]"
    If (CurrentProject.IsTrusted) Then
        Screen.ActiveControl = Null
    End If
    If (Form.FilterOn) Then
        DoCmd.RunCommand acCmdRemoveFilterSort
    End If
    DoCmd.SearchForRecord , "", acFirst, "[ID]=" & TempVars!ActiveControlValue
    TempVars.Remove "ActiveControlValue"


cboGoToContact_AfterUpdate_Exit:
    Exit Sub

cboGoToContact_AfterUpdate_Err:
    MsgBox Error$
    Resume cboGoToContact_AfterUpdate_Exit

End Sub


'------------------------------------------------------------
' cboGoToContact_GotFocus
'
'------------------------------------------------------------
Private Sub cboGoToContact_GotFocus()
On Error GoTo cboGoToContact_GotFocus_Err

    On Error Resume Next
    If (Screen.ActiveForm.Name = Form.Name) Then
        DoCmd.Requery Screen.ActiveControl.Name
    End If


cboGoToContact_GotFocus_Exit:
    Exit Sub

cboGoToContact_GotFocus_Err:
    MsgBox Error$
    Resume cboGoToContact_GotFocus_Exit

End Sub


'------------------------------------------------------------
' cmdEmail_Click
'
'------------------------------------------------------------
Private Sub cmdEmail_Click()
On Error GoTo cmdEmail_Click_Err

    On Error Resume Next
    DoCmd.SendObject , "", "", [Contact Name] & IIf(Nz([E-mail Address]) <> "", " [" & [E-mail Address] & "]"), "", "", "", "", True, ""
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If


cmdEmail_Click_Exit:
    Exit Sub

cmdEmail_Click_Err:
    MsgBox Error$
    Resume cmdEmail_Click_Exit

End Sub


'------------------------------------------------------------
' cmdSaveAsOutlookContact_Click
'
'------------------------------------------------------------
Private Sub cmdSaveAsOutlookContact_Click()
On Error GoTo cmdSaveAsOutlookContact_Click_Err

    On Error GoTo 0
    DoCmd.RunCommand acCmdSaveAsOutlookContact


cmdSaveAsOutlookContact_Click_Exit:
    Exit Sub

cmdSaveAsOutlookContact_Click_Err:
    MsgBox Error$
    Resume cmdSaveAsOutlookContact_Click_Exit

End Sub


'------------------------------------------------------------
' cmdSaveandNew_Click
'
'------------------------------------------------------------
Private Sub cmdSaveandNew_Click()
On Error GoTo cmdSaveandNew_Click_Err

    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.GoToControl "First Name"


cmdSaveandNew_Click_Exit:
    Exit Sub

cmdSaveandNew_Click_Err:
    MsgBox Error$
    Resume cmdSaveandNew_Click_Exit

End Sub


'------------------------------------------------------------
' cmdClose_Click
'
'------------------------------------------------------------
Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Err

    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    DoCmd.Close , ""


cmdClose_Click_Exit:
    Exit Sub

cmdClose_Click_Err:
    MsgBox Error$
    Resume cmdClose_Click_Exit

End Sub


'------------------------------------------------------------
' E_mail_Address_DblClick
'
'------------------------------------------------------------
Private Sub E_mail_Address_DblClick(Cancel As Integer)
On Error GoTo E_mail_Address_DblClick_Err

    On Error Resume Next
    If (Not IsNull(Screen.ActiveControl)) Then
        DoCmd.SendObject , "", "", Screen.ActiveControl, "", "", "", "", True, ""
    End If


E_mail_Address_DblClick_Exit:
    Exit Sub

E_mail_Address_DblClick_Err:
    MsgBox Error$
    Resume E_mail_Address_DblClick_Exit

End Sub

Open in new window

0
 

Author Closing Comment

by:zpotok
ID: 35488473
Thanks (once again) for your help.

sure wish they would come out with a A2010 service patch....
0
 
LVL 21
ID: 35517464
The Office 2010 SP1  should be coming out soon.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question