Hi all,

I'm trying to open a form (Form B) from another form (Form A). They're linked by similar fields (Conveyor) in two different tables (Table B and Table A respectively). I use a button with the source code below to open Form B from Form A.

Private Sub Conveyor_Config_Click()
On Error GoTo Err_Conveyor_Config_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Combo Starter Setup"

    GetInfo Conveyor, [Master Panel], [Starter], 1

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

Exit_Conveyor_Config_Click:
    Exit Sub

Err_Conveyor_Config_Click:
    MsgBox Err.Description
    Resume Exit_Conveyor_Config_Click

End Sub

I'm using a general module to house 3 global variables to transfer between Form A and Form B. That code follows:

Option Compare Database
Global GstrConveyor As String
Global GstrPanel As String
Global GstrType As String


Sub GetInfo(strCon As String, strPan As String, strTyp As String, Read As Boolean)

    'Assigns the conveyor to the global variable
    If (Read) Then
        GstrConveyor = strCon
        GstrPanel = strPan
        GstrType = strTyp
    Else
        strCon = GstrConveyor
        strPan = GstrPanel
        strTyp = GstrType
    End If

End Sub

And then within Form B, on open I'm trying to verify that the variable is not already listed. I couldn't figure out how to find first, then delete so I just delete it if it is there, then insert the new record. The problem occurs when the new record is inserted, it does not display in Form B (but it is in the Table) and Form waits at a new record. I tried using DoCmd.FindRecord to get me to the record but was given an error saying that function could not be used at that moment. Is there a way to remove the prior record and insert a new one and still be on the new record for editing? The code for Form B when it opens is below.

Private Sub Form_Open(Cancel As Integer)

    Dim strConveyor As String
    Dim strPanel As String
    Dim strType As String
    Dim strSQL As String
    Dim strTmp As String
    Dim iLen As Integer

    GetInfo strConveyor, strPanel, strType, 0

 ' DoCmd.RunSQL "SELECT[List Combo Starters].[Type] " & _
 ' "FROM[List Combo Starters] " & _
 ' "WHERE ([List Combo Starters].[Conveyor] = '" & strConveyor & "');"

    DoCmd.GoToRecord , , acFirst

    'Delete previous record of the combo starter to start from scratch
    DoCmd.RunSQL "DELETE[List Combo Starters].*,[List Combo Starters](" & _
            "Bus, MacID, Devicename, Panel, Type, Conveyor) " & _
            "FROM[List Combo Starters] " & _
            "WHERE ((([List Combo Starters].[Conveyor])= '" & strConveyor & "'));"

    'Adds new record to List Combo Starters table
    DoCmd.RunSQL "INSERT INTO[List Combo Starters](" & _
       "Conveyor, Devicename, Type, Panel) " & _
       "VALUES('" & strConveyor & "' , 'CM'&'" & strConveyor & "', '" & strType & "', '" & strPanel & "');"

End Sub




Thank you all in advance for your help.

Why not do the following to find your variable:

Create a boolean variable
Set boolean to No
Use your other variable
Open a recordset on the column.
Loop through the recordset.
     If the field = your variable, set boolean to yes
     End If
Loop
Check for boolean value
If no, insert record

Also, what are the On Open and On Load events of Form B? Are they opening the form to a new record?

Why not enter your values into your tables, then open a query where you list all the records with that value, including the PK, sort on the PK, DESC, Top 1, then open Form B on the PK returned by the query?



mmcdonal

How do you open Form B on the PK, because that's what it should be doing as is.

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

or am I incorrect? the Conveyor is the PK on both tables. I setup the loop with the bool variable like you said and that works very well, thank you. I don't think I follow on how to use the query to get the record on Form B differently than the way it is setup now. My other hesitation is I've got two text boxes setup on Form B linked to Table B that need to be able to update Table B. Could I still do that with a Query involved (I'm new to Access and have minor experience in DBs)?

mmcdonal,

Thanks for all your help! I finally got it all figured out. I'm not sure why I didn't look there sooner, but all I had to do was amend the one line from
    DoCmd.OpenForm stDocName, , , stLinkCriteria
to
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
so don't worrey about it. :)

-ermy78