By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 2,946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

Form1 to open Form2 with same records in form1?

P: 46
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
Expand|Select|Wrap|Line Numbers
  1. 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.*
  2. FROM tblContacts
  3. 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]));
  4.  
Query5
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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;
  3.  
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:
Expand|Select|Wrap|Line Numbers
  1. 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]; 
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command57_Click()
  2. Dim stDocName As String
  3. Dim strLastName, strUserName, strFirstName, strModelName, strComputerName As String
  4. Dim strChargeCode, strBusinessPhone, strAssetNumber, strSerialNumber As String
  5. stDocName = "frmSystemPrepChecklist"
  6.  
  7. 'Store the calling form's (frmSearchUsers) Lastname, FirstName and
  8. 'UserName, ModelName, ComputerName, AssetNumber, SerialNumber
  9. 'and ChargeCode, to add to new reocrd in 'frmSystemPrepChecklist', if needed
  10. strLastName = Me!LastName
  11. strUserName = Me!UserName
  12. strFirstName = Me!FirstName
  13. strModelName = Me!ModelName
  14. strComputerName = Me!ComputerName
  15. strChargeCode = Me!ChargeCode
  16. strBusinessPhone = Me!BusinessPhone
  17. strAssetNumber = Me!AssetNumber
  18. strSerialNumber = Me!SerialNumber
  19.  
  20. 'Open "frmSystemPrepChecklist", goto the matching UserName field, and set the focus to it.
  21. 'Note: the strUserName at the end of the following line is the OpenArgs property.
  22. 'It is the UserNmae I wish to locate in 'frmSystemPrepChecklist' and is by the DoCmd.FindRecord
  23. DoCmd.OpenForm stDocName, , , , acFormEdit, , strUserName
  24. Forms!frmSystemPrepChecklist!mainUserName.SetFocus
  25.  
  26. 'Assign frmSystemPrepChecklist mainUserName to a temp variable
  27. strmainUserName = Forms!frmSystemPrepChecklist!mainUserName
  28.  
  29. 'Find the first record in table2 (frmSystemPrepChecklist,
  30. 'that matches the UserName
  31. DoCmd.FindRecord strUserName, , True, , True, , True
  32.  
  33. 'If the UserName's do not match (not found in tabel2, then
  34. 'this must be a new record so add a new record and
  35. 'populate the listed fields of 'frmSystemPrepChecklist
  36.  
  37. If strmainUserName <> strUserName Then
  38. DoCmd.GoToRecord , , acNewRec
  39. Forms!frmSystemPrepChecklist!LastName = strLastName
  40. Forms!frmSystemPrepChecklist!FirstName = strFirstName
  41. Forms!frmSystemPrepChecklist!UserName = strUserName
  42. Forms!frmSystemPrepChecklist!BusinessPhone = strBusinessPhone
  43. Forms!frmSystemPrepChecklist!ModelName = strModelName
  44. Forms!frmSystemPrepChecklist!ChargeCode = strChargeCode
  45. Forms!frmSystemPrepChecklist!ComputerName = strComputerName
  46. Forms!frmSystemPrepChecklist!AssetNumber = strAssetNumber
  47. Forms!frmSystemPrepChecklist!SerialNumber = strSerialNumber
  48. End If
  49. End Sub
  50.  
But couldn't get it to work


Help please!
Thanks,
Mar 6 '09 #1
Share this Question
Share on Google+
9 Replies


maxamis4
Expert 100+
P: 295
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.?
Mar 9 '09 #2

P: 46
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!
Mar 9 '09 #3

puppydogbuddy
Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSystemPrepChecklist_Click()
  2. DoCmd.OpenForm "frmSystemPrepChecklist", , ,"[UserName] = "' & Forms!frmSearchUsers!UserName & "'"
  3. DoCmd.Close acForm, "frmSearchUsers"
  4. End Sub
  5.  
Mar 9 '09 #4

P: 46
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?
Mar 9 '09 #5

puppydogbuddy
Expert 100+
P: 1,923
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
Mar 9 '09 #6

P: 46
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSystemPrepCheck_Click()
  2. DoCmd.OpenForm "frmSystemPrepChecklist", , , "[UserName] = "" & Forms!frmSearchUsers!UserName & """
  3. DoCmd.Close acForm, "frmSearchUsers"
  4. End Sub
  5.  
Above code will open the new form but it opens blank
Mar 9 '09 #7

puppydogbuddy
Expert 100+
P: 1,923
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.
Mar 9 '09 #8

P: 46
ITS ALIVE!!!
lol
It works - Thanks PuppyDogBuddy!
Mar 9 '09 #9

puppydogbuddy
Expert 100+
P: 1,923
You're welcome. Glad you got it working!
Mar 9 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.