Return contents of a control in an Access form

[spiceAlex]

All I need to do is declare a variable that returns the contents of a field on my Access form. For example's sake, my variable name is myVar, the database is MyDB, the form is MyForm, and the control is MyControl.
Thanks

 

[CaptainD]

Can you provide a better explanation of what you want to do and post your code showing what you have tried.

 

[spiceAlex]

Thanks, CaptainD-
We've got phone software that, when the call comes in, it's dumped into a variable called remoteNumber. My VB code checks the number against what I have in my access database, and if it exists, my form pops up (6000 records) with the correct customer data of that caller. The field on the form that I need to compare is ContactMainPhone.

If there is no match I want a simple msgBox("No contact found.") to occur. I took one VB6.0 class about five years ago, am actually a Crystal Reports person, and know what I need to do, but don't know the syntax.

' //////////////////////////////////////////////////////////////////////////////
' // Microsoft VBScript for: Microsoft Access Database
' // Written by: Resource Software International (RSI) Limited
' // 11-MAY-2005
' //
' // Tested With: Win2K & Access2K
' //////////////////////////////////////////////////////////////////////////////
On Error Resume Next

Const acCmdAppMaximize = 10
Const acCmdAppMinimize = 11
Const acCmdAppRestore = 9

Const MM_DB_PATH = "C:\Documents and Settings\All Users\Documents\"
Const MM_DB_NAME = "nortelPhonePnl.mdb"
Const MM_DB_TBLE = "custServicePopup"
Const MM_DB_TBLE_CAP = "Sheet2"
Const MM_DB_TBLE_COL = "[ContactMainPhone]"


Dim iPID, hWnd, sDNIS, oAccess, WShShell, phone

' // Locate our MS Access with the appropriate form opened within it...
Function RsiGetAppPID()
RsiGetAppPID = WVrShell.FindWindowLike(MM_DB_TBLE_CAP, True)
If (RsiGetAppPID = 0) Then
RsiGetAppPID = WVrShell.FindWindowLike("Microsoft Access - [" + MM_DB_TBLE_CAP + "]", True)
End If
End Function

'sDNIS = chr(Right(Trim(WVrCall.CalledIdNumber), 4)) 'phone number of callee

'// Check if Microsoft Access is already running with the appropriate form open.
'// If the appropriate form is not open within Microsoft Access, we will start
'// another copy of Microsoft Access and open this form, so we do not adversely
'// affect any other Microsoft Access work in progress...
iPID = RsiGetAppPID()
phone = WVrCall.RemoteNumber
caller = "[ContactMainPhone] = '" & phone & "'"



If (iPID = 0) then
Set oAccess = CreateObject("Access.Application")
oAccess.OpenCurrentDatabase (MM_DB_PATH + MM_DB_NAME), False
oAccess.DoCmd.OpenForm MM_DB_TBLE,acNormal, , caller
iPID = RsiGetAppPID()

Else

Set oAccess = GetObject(MM_DB_PATH + MM_DB_NAME)
oAccess.DoCmd.ShowAllRecords

End If



' // Restore MS Access, just in case it was minimized...
oAccess.DoCmd.RunCommand acCmdAppRestore

If (iPID <> 0) Then
' // Bring MS Access to the foreground (Activate it)...
Set WShShell = CreateObject("WScript.Shell")
WShShell.AppActivate(iPID)
Set WShShell = Nothing
End If



' // Make sure this copy of MS Access is visible... and no records are filtered...
oAccess.Visible = True
oAccess.DoCmd.Maximize


' // Apply filter to display all records that match our criteria...
oAccess.DoCmd.ApplyFilter , (MM_DB_TBLE_COL + "=" + chr(WVrCall.RemoteNumber))

 

[CaptainD]

We've got phone software that, when the call comes in, it's dumped into a variable called remoteNumber. My VB code checks the number against what I have in my access database, and if it exists, my form pops up (6000 records) with the correct customer data of that caller. The field on the form that I need to compare is ContactMainPhone.

"ContactMainPhone" is a field in the database, not the form.

As I understand the script, it filters the form to the contacts phone number.

How does the form display the contact information? (TextBox, Listbox...)

Have you looked at the "Code" behind the form?

If so:
Is there any code in the Form open event?

Public variable that the "caller" number is passed to?(There should be)

As far as doing a query yourself, once you find the number that is passed when the form is opened, you could use DLookup() to search the database or create a query.

This could be done with the FormOpen event, a Click event etc. depending on how you want to do it.

 

[spiceAlex]

Hi CaptainD -
Thanks for all of your information. The form displays the contact phone as a text box. I've called both the text box and the field on the form the same 'ContactMainForm.' I have two or three subforms on the main form for notes and order/shipping information.

I have no code in the Form open event. All I have is code to open a number of buttons as hyperlinks. The form has customer and broker, order notes, shipping notes (with buttons linking to UPS and the like to track packages), and a place to enter current notes directly on the form. I'm sure it meets all the taboos of Access development (I'm a Crystal Reports person) but it all works except for the case when the number is not found (but you probably already get that).

The query I've done is not an access query. We use People Soft, and our customer service staff enters orders all day. I run a query to grab that data using a query tool and dump it into an Excel file, which I then import into Access.

 

[CaptainD]

So when the access program opens, I assume the form pops up and the phone number is in the textbox, correct?

And from there you want to search the database to see if that number is already in the table, correct?

This part of your script is filtering the form for that number. It should, in a round about way, do the search for you. If the number exist there would be a record.

' // Apply filter to display all records that match our criteria...
oAccess.DoCmd.ApplyFilter , (MM_DB_TBLE_COL + "=" + chr(WVrCall.RemoteNumber))

If not:

The following is under the click event of a command button and will run a query on the database to see how many copies of that number are in the table. If it returns 0 then the number is not in the database (or possibly not in the same format)

Note: Some programmers, myself included will prefix textbox names with "txt", command buttons with "cmd" etc. to identify them as a textbox or command button and the code below does the same. I recommend you change the name of your textbox. If not, edit my post below to the name of your textbox.

You will need to make a reference to Microsoft DAO for this to run. In the code editor menu click "Tools", "Reference" and check Microsoft DAO

Private Sub cmdCheckPhoneNumber_Click()
On Error GoTo ErrHandler
Dim sPhoneNumber As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

Set db = CurrentDb
strSql = "SELECT COUNT(1) FROM CustServicePopup WHERE ContactMainPhone = '"

'Check to make sure we have a number
txtContactMainPhone.SetFocus
If txtContactMainPhone.Text = "" Then
    MsgBox "No phone number to check!", vbOKOnly, "Attention"
    Exit Sub
Else
    sPhoneNumber = Trim(txtContactMainPhone.Text)
End If

Set db = CurrentDb
strSql = "SELECT COUNT(*)AS [RecCount] FROM CustServicePopup WHERE ContactMainPhone = '" & sPhoneNumber & "'"
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)


If rs.Fields("RecCount").Value = 0 Then
    MsgBox "No records found", vbOKOnly, "Attention"
Else
    MsgBox rs.Fields("RecCount").Value & " Records found", vbOKOnly, "Record exist"
End If

Set rs = Nothing
Set db = Nothing

Exit Sub
ErrHandler:
    MsgBox "Error checking phone number. Error #: " & Err.Number & ", " & Err.Description, vbOKOnly, "Error!"
    Set rs = Nothing
    Set db = Nothing
    
    End Sub

Now, assuming the above assumptions are not correct or you just want to do a check on your own, place this under the click event of a

 

[CaptainD]

Also, why don't you just use access to connect to your peoplesoft database instead of all the other work you are doing.

This is an example I found.

http://www.towson.edu/AdminFinance/...cess 2003/Access_Get_Data_From_Peoplesoft.pdf

 

[spiceAlex]

CaptainD, You're the best. Thanks for the code AND the prefix tip. Like I said, I'm a Crystal Developer and don't know VB...OBVIOUSSSSLLLyyy. Our PS database is hosted by our parent company out-of-state and I don't think they will give us direct access to dumping data directly from PS to Access. So alas, I didn't even begin to think along this line. I'll look into it today, as well.
Thanks again! I'll let you know how it goes.

 

[spiceAlex]

I looked at the 'get data directly from PS' link you sent which solidified my dilemma - we are not given access to Oracle, therefore, can't set up an ODBC connection to grab the data. With Crystal I do the same thing: I write one or more queries and bring those into Crystal as if they are my tables. It's cumbersome and slow. Additionally, we are stuck with Access 97 and Crystal 9.

 

[CaptainD]

You could use the Crystal DSN to get your data, it does not know what program is pulling the information through it, it is just a connection.

 

[spiceAlex]

I don't think it's necessary. I've already written the query and already getting the needed data. I'm going to figure out a .bat file to import the data. See

http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb07/hey0206.mspx