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,

 

[ProgramError]

Hi vanlanjl,

Put a break in line
DoCmd.OpenForm stDocName, , , , acFormEdit, , strUserName
and check what the value of strusername is.



Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.