Trying to get several fields in a form to auto fill at the click of a

[mhshane]

Using Access 2007 to track the company's wireless devices.
FrmMain has fields for Common Name, Dept, Cell Number, Datacard number, etc. It also has various buttons to launch other forms, queries or macros.
FrmMain is based off a query that queries various tables (tblUser, tblDevices, etc)

When a user calls in I can type the user's name or mobile number in the search fields at the top of FrmMain and the form will populate with all the user's info.
If the user has a problem (lost it, broke it, got it wet, and so on) I create a ticket by pressing a button titled "Cell Events".

The Cell Events button opens frmEvent-View with the following fields: Wireless Number, Event Date, User, Problem, and Event Description in Tabular format.
This form is based on tblEvents (not a query) and is filtered to display only events pertaining to the cell number populated in FrmMain .
Example: Sally Berry calls with a lost phone. I type in "Sally Berry" in the search field on FrmMain and populate the form with her info including her cell number, 999-123-9999
I click the Cell Events Button and frmEvent-View opens displaying two entries for mobile number 999-123-9999 and a blank entry beneath them for me to fill out. Whatever I type in those blank fields creates a new record in tblEvents.

Right now I have to type or copy/paste the info in.

I put a button at the top right hand corner of the frmEvent-View called[highlight] Add Event[/highlight]. When I click the button I would like for the Wireless Number and User name to auto fill based on username and cell number in FrmMain (which is open at the time) and for the Date to be filled in with the current date.

So basically I want VBA to do the following when I click on the[highlight] Add Event[/highlight] button:
1) Copy the text in [FrmMain].[Common Name] and paste in [frmEvent-View].[User]
2) Copy text in [FrmMain].[Cell Number] and paste in [frmEvent-View].[Wireless Number]
3) Put the current date in [frmEvent-View].[Event Date]

I'm new to VBA so bear with me here. I have tried the following:

Private Sub Add_Cell_Event_Click()

'Add the string variables,
Dim strCell As String
Dim strUser As String


'set the string variable to the contents of the text boxes
strCell = Forms!frmMain2![Cell Number]
strUser = Forms!frmMain2![Common Name]

'Copy set values above to the corresponsing text boxes on frmView Events
[Wireless Number] = strCell
[User] = strUser

END SUB

I have also tried these other syntax I found online:

frmMain![cell number] = Me![wireless number]

[Forms]![frmMain]![cell number] = [wireless number]

Form![frmMain]![cell number] = Me.[wireless number]


Any suggestions and tips on using VBA are most appreciated. Thanks.

 

[TheAceMan1]

How are ya crazyotter . . .

For starters (espcially since its not working), you speak of [blue]FrmMain[/blue]. Yet in your code you show [blue]frmMain2[/blue] ... Which is it?

In any event try:

[blue]Me.[Wireless Number] = strCell
Me.[User] = strUser[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mhshane]

Hey AceMan. I am doing well. Thanks for asking. Hope you had a great weekend.

FrmMain and FrmMain2 are copies of each other. I often need to see info on two users at the same time so I created a copy of frmMain.

Your suggestion worked. At first it did nothing (gave no error and did not populate the fields) then I realized why. Since I see all the previous events the active fields are on the top most entry. When I click in a field for the blank entry at the bottom the VBA works.
I need to add a command in the VBA to create a new entry in the table because I added a command to have the VBA populate the Event_Date field with the current date. I don't want someone to click the button and update the field on an existing record so I put an IF THEN statement to prevent. Would be better to have the VBA simply go to blank entry/add new entry to begin with.

I realize now that there are a few dynamics I have to deal with. Since I have two copies of frmMain which open up the frmEvents-View the VBA code as it is will always looks to frmMain for the info to fill in. So I either need to make duplicates, i.e. frmEvents-View2 opens from the macro button on frmMain2 and has it's own VBA code - OR -
I need to put in some code that says "check which form (frmMain or frmMain2) I clicked the button from".

The other dynamic is all our sales reps have both cell phones and datacards. So frmMain and frmMain2 display info for Cell device and Datacard device.
Clicking on the "Cell Events" button opens frmEvents-View and filters on the cell number.
Clicking on the "Datacard Events" button opens frmEvents-View and filters on the datacard.
So the VBA code will need to know what button I pressed and from which form in order to get the right info copied/pasted.

Current code. Adds the wireless number, user name and current date successfully as long as I click the blank entry at the bottom of the form.

[navy]
Private Sub Add_Cell_Event_Click()

'Add the string variables,
Dim strCell As String
Dim strUser As String


'set the string variable to the contents of the text boxes
strCell = Forms!frmMain![Cell Number]
strUser = Forms!frmMain![Common Name]

'Copy set variables above to the corresponsing text boxes on frmView Events
Me.[Wireless Number] = strCell
Me.[User] = strUser

'add current date to the Date field
If IsNull(Me.Event_Date) Then
Me.Event_Date = Date
Else
MsgBox ("Select blank record")
End If

End Sub
[/navy]

So next steps for this VBA button:
[ol]
[li]Change "Add Cell Event" to generic "Add Event" button and Tell the "Add Event" Button to go to the blank entry at the bottom of frmEvents-View"[/li]
[li]Add current date and fix where the button cannot update an existing record by accident. Think I got this one but feel free to suggest something better.[/li]
[li]Tell VBA to determine if I clicked the "Cell Events" button or the "Datacard Events" button[/li]
[li]Tell VBA to determine which form I clicked the "Cell Events" button or the "Datacard Events" button from.[/li]
[/ol]

[red]Thanks for getting me started. Any further suggestions would be greatly appreciated?[/red]