Form1 to open Form2 with same records in form1?

[vanlanjl]

Okay lets see if I can do this with out confusing myself or others. First I will give ALL the details then state my problem and request at the bottom.

Tables:

tblContacts
ID
Company
LastName
FirstName
Initial
E-MailAddress
JobTitle
BuisnessPhone
HomePhone
MobilePhone
faxNumber
Address
City
State/Province
Zip/PostalCode
UserName (PK)
ChargeCode (FK)
LocationCode (FK)

tblAssets
ID (PK)
AssetNumber
SerialNumber
ComputerName
DeploymentDate
Active
UserName (FK)
OfficeName (FK)
OSName (FK)
ModelName (FK)

tblModel
ModelID (PK)
ModelName

tblOSVersion
OSName (PK)

tblOfficeVersion
OfficeID (PK)
OfficeName

tblLocation
LocationCode (PK)

tblChargeCode
ChargeCode (PK)

** Referential Integrity is Enforced on All tables**

Query1

SELECT IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])) AS [File As], IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])) AS [Contact Name], tblContacts.*
FROM tblContacts
ORDER BY IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])), IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]));
 

Query5

SELECT tblContacts.LastName+' '+tblContacts.FirstName+', '+tblContacts.Initial AS Expr1, tblContacts.UserName, tblAssets.ComputerName, tblContacts.LocationCode, tblContacts.ChargeCode, tblAssets.AssetNumber, tblAssets.SerialNumber, tblContacts.[E-mailAddress], tblContacts.JobTitle, tblContacts.BusinessPhone, tblContacts.Address, tblContacts.City, tblContacts.[State/Province], tblContacts.[ZIP/Postal Code], tblOfficeVersion.OfficeName, tblAssets.OSName, tblModel.ModelName
FROM tblOfficeVersion INNER JOIN (tblModel INNER JOIN (tblContacts INNER JOIN tblAssets ON tblContacts.UserName = tblAssets.UserName) ON tblModel.ModelID = tblAssets.ModelName) ON tblOfficeVersion.OfficeID = tblAssets.OfficeName;
 

Forms (only listing three i am concerned with for question)

frmSearchUsers
Record Source: Query1
Fields:
txtFileAs
txtContactName
txtID
txtCompany
txtLastName
txtFirstName
txtInitial
txtE-MailAddress
txtJobTitle
txtBuisnessPhone
txtHomePhone
txtMobilePhone
txtfaxNumber
txtAddress
txtCity
txtState/Province
txtZip/PostalCode
txtUserName (PK)
txtChargeCode (FK)
txtLocationCode (FK)

frmAssets
txtAssetNumber
txtSerialNumber
txtComputerName
txtDeploymentDate
txtActive
txtUserName (FK)
txtOfficeName (FK)
txtOSName (FK)
txtModelName (FK)

frmSystemPrepChecklist
Record Source:

SELECT tblContacts.[LastName], tblContacts.[FirstName], tblContacts.[ChargeCode], tblContacts.[BusinessPhone], tblContacts.[UserName], tblAssets.[AssetNumber], tblAssets.[SerialNumber], tblAssets.[ModelName], tblAssets.[ComputerName] FROM tblContacts LEFT JOIN tblAssets ON tblContacts.UserName=tblAssets.[UserName]; 
 

Fields:
txtLastName
txtFirstName
txtUserId
txtComputerName
cboModelName
txtAssetNumber
txtSerialNumber
txtBuisnessPhone
txtChargeCode

Okay so here si the deal. The user will open "frmAssets and will enter in new data to the fileds and save form and return to switchboard then will open "frmSearchUser" and either search for current user or will enter new user infor by using cmdAddFromOutlook button. Then pick user they want to add and push okay then search for user in list and then the text boxes are popualted with user info. There is a button "cmdSystemPrepChecklist", upon clicking this it will open the frmSystemPrepChecklist.

What happens is when the frmSystemPrepChecklist opens it automatically open to the first record, then you have to go down to the record selector at the bottom of the form and run a search for who you are looking for. What I want it to do is, I want the frmSystemPrepChecklist to open with the same user information that was in the prevous form, frmSearchUsers.
I tried the following code:

Private Sub Command57_Click()
Dim stDocName As String
Dim strLastName, strUserName, strFirstName, strModelName, strComputerName As String
Dim strChargeCode, strBusinessPhone, strAssetNumber, strSerialNumber As String
stDocName = "frmSystemPrepChecklist"
 
'Store the calling form's (frmSearchUsers) Lastname, FirstName and
'UserName, ModelName, ComputerName, AssetNumber, SerialNumber
'and ChargeCode, to add to new reocrd in 'frmSystemPrepChecklist', if needed
strLastName = Me!LastName
strUserName = Me!UserName
strFirstName = Me!FirstName
strModelName = Me!ModelName
strComputerName = Me!ComputerName
strChargeCode = Me!ChargeCode
strBusinessPhone = Me!BusinessPhone
strAssetNumber = Me!AssetNumber
strSerialNumber = Me!SerialNumber
 
'Open "frmSystemPrepChecklist", goto the matching UserName field, and set the focus to it.
'Note: the strUserName at the end of the following line is the OpenArgs property.
'It is the UserNmae I wish to locate in 'frmSystemPrepChecklist' and is by the DoCmd.FindRecord
DoCmd.OpenForm stDocName, , , , acFormEdit, , strUserName
Forms!frmSystemPrepChecklist!mainUserName.SetFocus
 
'Assign frmSystemPrepChecklist mainUserName to a temp variable
strmainUserName = Forms!frmSystemPrepChecklist!mainUserName
 
'Find the first record in table2 (frmSystemPrepChecklist,
'that matches the UserName
DoCmd.FindRecord strUserName, , True, , True, , True
 
'If the UserName's do not match (not found in tabel2, then
'this must be a new record so add a new record and
'populate the listed fields of 'frmSystemPrepChecklist
 
If strmainUserName <> strUserName Then
DoCmd.GoToRecord , , acNewRec
Forms!frmSystemPrepChecklist!LastName = strLastName
Forms!frmSystemPrepChecklist!FirstName = strFirstName
Forms!frmSystemPrepChecklist!UserName = strUserName
Forms!frmSystemPrepChecklist!BusinessPhone = strBusinessPhone
Forms!frmSystemPrepChecklist!ModelName = strModelName
Forms!frmSystemPrepChecklist!ChargeCode = strChargeCode
Forms!frmSystemPrepChecklist!ComputerName = strComputerName
Forms!frmSystemPrepChecklist!AssetNumber = strAssetNumber
Forms!frmSystemPrepChecklist!SerialNumber = strSerialNumber
End If
End Sub
 

But couldn't get it to work


Help please!
Thanks,

[maxamis4]

So let me get this correct. You want to use the first and last name to populate your data in your data base from outlook? I am getting this correct.?

[vanlanjl]

Hmm not sure if I understand your question as simple as it is. Lets see if i can clarify for you. When I push the cmdAdFromOutlook button it opens the search window in outlook. I type in the users name I looking for then click add and it automatically adds it to my tblContacts. BUT on my form frmSearchUsers, instead of populating the fields with the NEW user that was just added it goes to the first user in the table. I want it to be populated with the user i just added.

There ia a macro running. The first one is the AddFromOutlook macro wich is built in to Access.

As it stands now the user has to go to acombo box on the form and search for the new user. When the name is selected it then fills in the fields with the correct data.


Then the user will push a button titled cmdSystemPrepCHecklist wich will open the frmSystemPrepChecklist. But again the frm opens to the first record in the tblCOntacts instead of the record that is displayed in the prior form frmSearchUsers. I want the second form to open to the information displyed in the prior form.

Does that help explain it? I hopeso. Thanks for interest and resposne!

[puppydogbuddy]

If I understand you correctly, I think you made it way more complicated then you needed to. You don't want to add a new record, you just want the second form to display the same info as the search form. The openform command has an argument that allows you to place the equivalent of a where clause, so here is all you need to do. I am assuming UserName is the key to the records in both Table1 and Table2; if it isn't substitute the record key for UserName.

Don't close frmSearchUsers until you have opened the frmSystemPrepChecklist in your cmdSystemPrepChecklist_Click() event.

Private Sub cmdSystemPrepChecklist_Click()
DoCmd.OpenForm "frmSystemPrepChecklist", , ,"[UserName] = "' & Forms!frmSearchUsers!UserName & "'"
DoCmd.Close acForm, "frmSearchUsers"
End Sub
 

[vanlanjl]

Run-time error '3075':
Syntax error (missing operator) in Query expression '[UserName]='.

Would it be because UserName is not a field on the frmSystemPrepChecklist?

Doo i need to add it? and make it invisable to the user?

[puppydogbuddy]

No, I think the problem is that I have a single and double quote in the wrong order.

Change this part of line 2:
"[UserName] = "' >>> double quote, then double quote/single quote

To this: "[UserName] = '" s/b double quote, then single quote/double quote

[vanlanjl]

Private Sub cmdSystemPrepCheck_Click()
DoCmd.OpenForm "frmSystemPrepChecklist", , , "[UserName] = "" & Forms!frmSearchUsers!UserName & """
DoCmd.Close acForm, "frmSearchUsers"
End Sub
 

Above code will open the new form but it opens blank

[puppydogbuddy]

You did not follow the instructions I gave you.

Change this part of line 2 in the code:
>>>>>>"[UserName] = "" & Forms!frmSearchUsers!UserName & """
To This:
>>>>>>"[UserName] = '" & Forms!frmSearchUsers!UserName & "'"

If the above correction does not fix it, make sure UserName is a control on the form frmSearchUsers. If UserName is on the form, it should work whether the control is visible or not visible.

[vanlanjl]

ITS ALIVE!!!
lol
It works - Thanks PuppyDogBuddy!

[puppydogbuddy]

You're welcome. Glad you got it working!