VBA Screen

Hello, I just finished a project that creates forms dynamically and my only problem is that the VBA editor screen keeps poping up every time a form is created, anybody know how to fix this?

Thanks in advance!

Hmmm nope, it runs without errors, loads the form correctly according to what I said in the query that generates it....it just pops me the window of code, I close it manually and start working with the form, but I want it not to appear or to be able to close it by code...

What's the code?

Function Select4Preview()
Dim rsR, rsRToolSQL
Dim dbs As Database
Dim strSQL, ToolSQL As String
Dim frm
Dim X, X500
Dim ID
Dim InDate
Dim Press
Dim Item
Dim Qty
Dim Resin
Dim Tool
Dim WO
Dim ET
Dim SU
Dim PH
Dim SSD
Dim Acum
Dim SCD
Dim ASD
Dim ACD
Dim Delta
Const strCB = "cb"
Const strID = "txtID"
Const strInDate = "txtInDate"
Const strPress = "txtPress"
Const strItem = "txtItem"
Const strQty = "txtQty"
Const strResin = "txtResin"
Const strTool = "txtTool"
Const strWO = "txtWO"
Const strET = "txtET"
Const strSU = "txtSU"
Const strPH = "txtPH"
Const strSSD = "txtSSD"
Const strAcum = "txtAcum"
Const strSCD = "txtSCD"
Const strASD = "txtASD"
Const strACD = "txtACD"
Const Tlbl = 50
Const lID = 400
Const lInDate = 900
Const lPress = 2000
Const lWO = 2900
Const lItem = 3700
Const lQty = 4900
Const lResin = 5750
Const lTool = 7000
Const lEfT = 7700
Const lSU = 8700
Const lPH = 9400
Const lSSD = 10850
Const lAcum = 10000
Const lSCD = 13000
Const lASD = 15200
Const lACD = 15700
Const cmdP = 3500
Dim ctl As Control
Dim T, W, h
Dim x1
Dim ToolValue

Dim opc
opc = Forms("frmImport").Controls("cmbPress").Value

Set dbs = CurrentDb
strSQL = "SELECT * FROM Press WHERE Press='" & opc & "' ORDER BY SSD"
Set frm = CreateForm(, strSQL)
    frm.DefaultView = 1
    DoCmd.OpenForm "Form1"
Set rsR = CurrentDb.OpenRecordset(strSQL)

With rsR
    .MoveFirst
    X = 1
    Do While Not .EOF
    T = 300 * X
        InDate = rsR.Fields(1)
        Press = rsR.Fields(2)
        Item = rsR.Fields(3)
        Qty = rsR.Fields(4)
        Resin = rsR.Fields(5)
        Tool = rsR.Fields(6)
        WO = rsR.Fields(7)
        ET = rsR.Fields(8)
        SU = rsR.Fields(9)
        PH = rsR.Fields(10)
        SSD = rsR.Fields(11)
        Acum = rsR.Fields(12)
        SCD = rsR.Fields(13)
        ASD = rsR.Fields(14)
        ACD = rsR.Fields(15)
        DoCmd.OpenForm "Form1", acDesign
        
        Set ctl = CreateControl("Form1", ControlType:=acCheckBox, lEfT:=200, Top:=T, Width:=200, Height:=300)
        ctl.Name = strCB & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lID, Top:=T, Width:=750, Height:=300)
        ctl.Name = strID & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lInDate, Top:=T, Width:=1100, Height:=300)
        ctl.Name = strInDate & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lPress, Top:=T, Width:=900, Height:=300)
        ctl.Name = strPress & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lWO, Top:=T, Width:=900, Height:=300)
        ctl.Name = strWO & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lItem, Top:=T, Width:=1200, Height:=300)
        ctl.Name = strItem & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lQty, Top:=T, Width:=850, Height:=300)
        ctl.Name = strQty & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lResin, Top:=T, Width:=1500, Height:=300)
        ctl.Name = strResin & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lTool, Top:=T, Width:=1000, Height:=300)
        ctl.Name = strTool & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lEfT, Top:=T, Width:=1000, Height:=300)
        ctl.Name = strET & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lSU, Top:=T, Width:=800, Height:=300)
        ctl.Name = strSU & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lPH, Top:=T, Width:=800, Height:=300)
        ctl.Name = strPH & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lAcum, Top:=T, Width:=850, Height:=300)
        ctl.Name = strAcum & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lSSD, Top:=T, Width:=2300, Height:=300)
        ctl.Name = strSSD & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lSCD, Top:=T, Width:=2300, Height:=300)
        ctl.Name = strSCD & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lASD, Top:=T, Width:=500, Height:=300)
        ctl.Name = strASD & X
        
        Set ctl = CreateControl("Form1", ControlType:=acTextBox, lEfT:=lACD, Top:=T, Width:=500, Height:=300)
        ctl.Name = strACD & X
        
        RunCommand acCmdFormView
        Forms("Form1").Controls(strInDate & X).Value = InDate
        Forms("Form1").Controls(strPress & X).Value = Press
        Forms("Form1").Controls(strItem & X).Value = Item
        Forms("Form1").Controls(strQty & X).Value = Qty
        Forms("Form1").Controls(strResin & X).Value = Resin
        Forms("Form1").Controls(strTool & X).Value = Tool
        Forms("Form1").Controls(strWO & X).Value = WO
        Forms("Form1").Controls(strET & X).Value = ET
        Forms("Form1").Controls(strSU & X).Value = SU
        Forms("Form1").Controls(strPH & X).Value = PH
        Forms("Form1").Controls(strSSD & X).Value = SSD
        Forms("Form1").Controls(strAcum & X).Value = Acum
        Forms("Form1").Controls(strSCD & X).Value = SCD
        Forms("Form1").Controls(strASD & X).Value = ASD
        Forms("Form1").Controls(strACD & X).Value = ACD
        X = X + 1
        .MoveNext
    Loop
    DoCmd.OpenForm "Form1", acDesign
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=200, Top:=Tlbl, Width:=200, Height:=300)
    ctl.Name = "lblSelect"
    Forms("Form1").Controls("lblSelect").Caption = "•"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lID, Top:=Tlbl, Width:=750, Height:=300)
    ctl.Name = "lblID"
    Forms("Form1").Controls("lblID").Caption = "ID"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lInDate, Top:=Tlbl, Width:=1100, Height:=300)
    ctl.Name = "lblInDate"
    Forms("Form1").Controls("lblInDate").Caption = "Date"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lPress, Top:=Tlbl, Width:=1100, Height:=300)
    ctl.Name = "lblPress"
    Forms("Form1").Controls("lblPress").Caption = "Press"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lItem, Top:=Tlbl, Width:=1200, Height:=300)
    ctl.Name = "lblItem"
    Forms("Form1").Controls("lblItem").Caption = "Item"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lQty, Top:=Tlbl, Width:=850, Height:=300)
    ctl.Name = "lblQty"
    Forms("Form1").Controls("lblQty").Caption = "Qty"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lResin, Top:=Tlbl, Width:=1200, Height:=300)
    ctl.Name = "lblResin"
    Forms("Form1").Controls("lblResin").Caption = "Resin"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lTool, Top:=Tlbl, Width:=850, Height:=300)
    ctl.Name = "lblTool"
    Forms("Form1").Controls("lblTool").Caption = "Tool"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lWO, Top:=Tlbl, Width:=900, Height:=300)
    ctl.Name = "lblWO"
    Forms("Form1").Controls("lblWO").Caption = "WO"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lEfT, Top:=Tlbl, Width:=850, Height:=300)
    ctl.Name = "lblET"
    Forms("Form1").Controls("lblET").Caption = "Ef. Time"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lSU, Top:=Tlbl, Width:=800, Height:=300)
    ctl.Name = "lblSU"
    Forms("Form1").Controls("lblSU").Caption = "Set Up"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lPH, Top:=Tlbl, Width:=800, Height:=300)
    ctl.Name = "lblPH"
    Forms("Form1").Controls("lblPH").Caption = "Hours"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lSSD, Top:=Tlbl, Width:=2000, Height:=300)
    ctl.Name = "lblSSD"
    Forms("Form1").Controls("lblSSD").Caption = "SSD"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lAcum, Top:=Tlbl, Width:=850, Height:=300)
    ctl.Name = "lblAcum"
    Forms("Form1").Controls("lblAcum").Caption = "Acum."
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lSCD, Top:=Tlbl, Width:=2000, Height:=300)
    ctl.Name = "lblSCD"
    Forms("Form1").Controls("lblSCD").Caption = "SCD"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lASD, Top:=Tlbl, Width:=500, Height:=300)
    ctl.Name = "lblASD"
    Forms("Form1").Controls("lblASD").Caption = "ASD"
    
    Set ctl = CreateControl("Form1", ControlType:=acLabel, lEfT:=lACD, Top:=Tlbl, Width:=500, Height:=300)
    ctl.Name = "lblACD"
    Forms("Form1").Controls("lblACD").Caption = "ACD"
    
    
    Set ctl = CreateControl("Form1", ControlType:=acCommandButton, lEfT:=cmdP, Top:=T + 450, Width:=1200, Height:=600)
    ctl.Name = "cmdPreview"
    Forms("Form1").Controls("cmdPreview").Caption = "Preview Selection"
    
    Dim mdl As Module
    Dim lngReturn
    Set ctl = Forms("Form1").Controls("cmdPreview")
    Set frm = Forms("Form1")
    Set mdl = Forms("Form1").Module
    lngReturn = mdl.CreateEventProc("Click", ctl.Name)
    mdl.InsertLines lngReturn + 1, "SelectPreview"
    
    X500 = 1
    Do Until X500 = X
    Set ctl = Forms("Form1").Controls(strID & X500)
    Set frm = Forms("Form1")
    Set mdl = Forms("Form1").Module
    lngReturn = mdl.CreateEventProc("AfterUpdate", ctl.Name)
    mdl.InsertLines lngReturn + 1, "OnUpdate"
    X500 = X500 + 1
    Loop
    RunCommand acCmdFormView
End With

Forms("Form1").RecordSelectors = False
x1 = 1
Do Until x1 = (X)
Forms("Form1").Controls(strCB & x1).Value = False
x1 = x1 + 1
Loop

x1 = 1
Do Until x1 = (X)
    If x1 = 1 Then GoTo Line1
    If Forms("Form1").Controls(strResin & x1).Value <> Forms("Form1").Controls(strResin & x1 - 1).Value Then
        Forms("Form1").Controls(strResin & x1).BackColor = RGB(255, 195, 15)
    Else
    End If
Line1:
    x1 = x1 + 1
Loop
Set rsR = Nothing

x1 = 1
Do Until x1 = (X)
ToolSQL = "SELECT Time FROM tblPress_Rel WHERE Item='" & Forms("Form1").Controls(strItem & x1).Value & "'"
        Set rsRToolSQL = CurrentDb.OpenRecordset(ToolSQL)
            If rsRToolSQL.Fields.Count = 1 Then
                ToolValue = rsRToolSQL.Fields(0)
                If ToolValue < 60 Then
                    Forms("Form1").Controls(strTool & x1).BackColor = RGB(250, 240, 45) 'Yellow
                Else
                    Forms("Form1").Controls(strTool & x1).BackColor = RGB(200, 35, 35) 'Red
                End If
            End If
        Set rsRToolSQL = Nothing
    x1 = x1 + 1
Loop

'x1 = 1
'Do Until x1 = (X)
'    If x1 = 1 Then GoTo Line2
'    If Forms("Form1").Controls(strTool & x1).Value <> Forms("Form1").Controls(strTool & x1 - 1).Value Then
'        Forms("Form1").Controls(strTool & x1).BackColor = RGB(255, 255, 255)
'    Else
'    End If
'Line2:
'    x1 = x1 + 1
'Loop
'Set rsR = Nothing

End Function

I use this same code 3 times, this one is to generate an extract from a table....I needed to do it like this because on a continuos form I wasn't able to paint a textbox a certain color depending on the value from the preceding record

the next time I use a very similar code to this, is to generate the preview from this first form, and if everything is alright, then save the data to a new table and drop the previous one

the last time I need this code is to update the date values from these saved records....

If there's an easier way to do all this I'd appreciate if you can share that knowledge with me, this database has to be split, then shared with 5 users and I don't want it to keep crashing (that's another problem, sometimes after hitting the save button, it saves the records, crashes, and when it restarts the file is corrupted)

In Microsoft Access the CurrentDb method establishes a hidden reference to the Microsoft Office 12.0 Access Conectivity Engine object library in a Microsoft Access database.

I didn't know about the library...got the reference from here:
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

Yes...

I need to be able to paint the background of the textboxes on the Tool Field in yellow or red if the preceding record is different from the actual record, and the Resin Field needs to be colored in orange if its value is different from the preceding record (first record is always painted, comparinsons start from record 2 to record N)

If I did the form as a continuos form it would paint all the textboxes (even with the loop cycle) with the last color from the last comparison, and that doesn't work with what I was required to do. So the only option I had was to select the data from the table, and based on the number of records retreived, generate them via VBA with a do while cycle, and that way I had individual textboxes for each record, each with its own unique name, and therefore I'm now able to make the coloring of the texboxes as I need to...

yes, same result, it colored all the records textboxes, even if the values were the same, they were both colored, and that doesn't work for the project...

FYI...I started this project in Excel, it was way easier there (comparing one cell to another instead of a record in a recordset), but they told me it needed to be a multiuser system, so the Excel macro wasn't the best option, that's why I created it with Access

Well, the poping vba screen is still an issue, but I think I managed to prevent the DB to keep crashing over and over again, I just needed to set all the variables and controls to Nothing before the End Function and it appears the problem is solved

Also, I changed this

DoCmd.SetWarnings False
DoCmd.Save acForm, "Form1"
DoCmd.Close acForm, "Form1"
DoCmd.Rename "frmtSetDates", acForm, "Form1"
DoCmd.SetWarnings True
DoCmd.DeleteObject acForm, "frmtSetDates"
DoCmd.OpenForm "frmProduction", acNormal

to this

DoCmd.SetWarnings False
'DoCmd.Save acForm, "Form1"
DoCmd.Close acForm, "Form1", acSaveNo
'DoCmd.Rename "frmtSetDates", acForm, "Form1"
DoCmd.SetWarnings True
'DoCmd.DeleteObject acForm, "frmtSetDates"
DoCmd.OpenForm "frmProduction", acNormal

By deleting / commenting the lines shown here, it appears to work faster and without the risk of crashing the DB, I use this similar block of instructions 3 times for 3 different scenarios in the system, and after 2 hours of testing, no crashes have been registered.