Select item on one form & transfer it to second form-2tbls

[hytina]

Hello,

I have a form with a combo box that allows the user to select a part number from a list that is a query of table 'A' and when the part is selected I would like it to drop into the next form which updates table 'B'. There is a macro on the combo box located at the OnClick of the form, I thought would work and it doesn't.

[ToolID]=[Forms]![frmOTMPselectnew]![Combo3] is the where condition, the form stated above this is frmOTMNew, data mode is Add. Table 'A' is an imported table from another database. Both tables have the same field names and are set as primary keys.

If anyone has a solution I would really appreciate it. I do not have any programming experience which has made this a bit more challenging.

 

[randysmid]

Hi,
I added this code to a command button on a form that will pass the SSN to another form:

'******** CODE STARTS HERE **************************
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPersonnelOPENtest"

stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
'************* END OF CODE **************************


HTH,
Randy Smith, MCP
California Teachers Association

 

[hytina]

Thank you for replying randysmid. I am working on your suggestion today. I have just a couple stupid questions. One, do you suggest adding this code to the 'OnClick' property of the combo box on the form that is used to select a ToolID? Second, DocName, is that the name of the form I want the ToolID to drop into or the name of the form that I am selecting the ToolID from?

I tried to set the code in the 'OnClick' of the Select form and set the DocName as the form I want the ToolID to drop into. I received this error message: Open Offsite Maintenance can't find the field 'ToolID' referred to in your expression. I have pasted the code I entered below. Please let me know any ideas. Thank you for your help.


Private Sub Combo3_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmOTMNew"

stLinkCriteria = "[ToolID]=" & "'" & Me![ToolID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

 

[randysmid]

Hi,
No, the code should go into the AfterUpdate event for the combo box. This happens after the user has selected a part number.

"stDocName" is simply a string variable that is used to name a form. The same is true for the criteria. For some inexplicable reason, Microsoft prefers a string variable to the actual form name and criteria.

HTH, Randy (MCP)

 

[hytina]

Thank you. This is working beautifully. I appreciate your help. I had put the wrong name of the field for the select form [ToolID] s/b [Combo3].


hytina

 

[hytina]

randysmid

I noticed a possible problem with the program and would like to know what you think. It seems to only transfer the ToolID number to the next form if the the ID is in both tables of both forms. In my case I will need to add a tool from 1st form/tbl to 2nd form/tbl. Once the new tool is selected in the list box (table A) it will need to be added to table B and seen on the next form.

Should the code you suggested be doing this?


hytina

 

[hytina]

randysmid

Also, in addition to adding the record from first table (A)/form to second table/form is it possible to have an error message if the part selected is already in second table (B)?

Thank you for your time randysmid.


hytina

 

[randysmid]

Yes,
You can write an SQL set of code to check almost anything. Here is what I used to look up a user in a table called "User_security", then determined if their password matched what was in the table:

'Open the user_security table
Dim DbInfo As Database
Dim rstSecurity As Recordset
Set DbInfo = CurrentDb()
Set rstSecurity = DbInfpenRecordset("User_security", dbReadOnly)

' Find the record that matches the control.
rstSecurity.FindFirst "[User] = '" & Me![temp_initials] & " '"
If rstSecurity.NoMatch Then
MsgBox "Invalid login, no such user!", vbOKOnly + vbCritical
DoCmd.Quit acQuitSaveNone
End If

If rstSecurity![user_password] = temp_password Then
'MsgBox "Valid login", vbOKOnly + vbInformation
gstrUser = Me!temp_initials.Value
gstrUserName = rstSecurity![user_name]
gstrUserLevel = rstSecurity![user_level]
'DoCmd.Close acForm, Me.NAME
Me.Visible = False
DoCmd.OpenForm "Menu", , , , , acWindowNormal
Else
MsgBox "Invalid password, try again or Quit Application!", vbOKOnly + vbCritical
temp_password.SetFocus
End If

HTH, Randy

HTH,
Randy Smith, MCP
California Teachers Association