form does not display when it is opened

[ewart strain]

Hey guys, I'm a newbie to the forum and to access and require some major help. I accepted a new position with my co. with the chance to broaden my horizon but it has been more an up hill battle than I expected. With that said here is my dilemma: I inherited a DB that pretty complex with forms and loads of tables and queries, most of which I somewhat understand. I have a form that allows me and any other user to update the DB, however it does not display when it is opened. the same form when copied to another DB opens with out any problem.
can anyone assist me in the matter?
P.s if additional info is needed i.e., code etc please don't hesitate to ask.
Any help will be greatly appreciated.

[MMcCarthy]

Hey guys, I'm a newbie to the forum and to access and require some major help. I accepted a new position with my co. with the chance to broaden my horizon but it has been more an up hill battle than I expected. With that said here is my dilemma: I inherited a DB that pretty complex with forms and loads of tables and queries, most of which I somewhat understand. I have a form that allows me and any other user to update the DB, however it does not display when it is opened. the same form when copied to another DB opens with out any problem.
can anyone assist me in the matter?
P.s if additional info is needed i.e., code etc please don't hesitate to ask.
Any help will be greatly appreciated.

If the form works ok in another database it is probably not a corruption issue. That's the good news (LOL)

OK

How is this form being opened in the database? Is it through a switchboard, another form or by just clicking on the form in the list?

If it is through another form I will need to see the code behind the command button used to open the form.

I will also need to see any code behind the on load and on open events of the form being opened.

This could take a while to figure out.

[ewart strain]

It's being opened from another form by clicking on the form.

Here's the code:

 
Private Sub Form_Load()
'Clean_House
Dim rs As Recordset
 
    Form_frmMain.Caption = Replace(CurrentProject.Name, "HRDB.mdb", "") & " - " & Form_frmMain.Caption
    Set rs = New Recordset
    prevForm = Me.Name
 
    If CurrALevel = "Administrator" Or CurrALevel = "Developer" Then
        If reLogin = True Then
            cmdAdmin.Visible = True
            cmdLogin.Visible = True
            cmd570.Visible = True
            Me.lblUser.Caption = "User: " & CurrName & " -- Level: " & CurrALevel
        End If
        cmdGSMUpdt.Visible = True
    Else
        cmdAdmin.Visible = False
        cmdLogin.Visible = False
        cmdGSMUpdt.Visible = False
        Me.lblUser.Caption = ""
    End If
 
    If CurrRDRUpdt = True Then
        cmdUpdateRDR.Visible = True
    Else
        cmdUpdateRDR.Visible = False
    End If
 
    If CurrSTView = True Then
        cmdStaffing.Visible = True
    Else
        cmdStaffing.Visible = False
    End If
 
    Load_Config_Info
    cboReport.RowSource = ""
    rs.Open "select * from ReportList order by displayname", CurrentProject.Connection
    rs.MoveFirst
 
    Do Until rs.EOF
        If IsNull(rs!securitycriteria) = True Then
            Me.cboReport.AddItem rs!DisplayName
        ElseIf rs!securitycriteria = "GSM" And CurrGSMView = True Then
            Me.cboReport.AddItem rs!DisplayName
        ElseIf rs!securitycriteria = "LBM" And CurrLBMView = True Then
            Me.cboReport.AddItem rs!DisplayName
        ElseIf rs!securitycriteria = "RDRConf" And CurrRDRConf = True Then
            Me.cboReport.AddItem rs!DisplayName
        ElseIf rs!securitycriteria = "PEND" And CurrPendVu = True Then
            Me.cboReport.AddItem rs!DisplayName
        Else
            'Nothing
        End If
        rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
    cboReport.Value = "Please Choose A Report from this dropdown list"
    reLogin = False
End Sub
-------------------------------------------
 
Private Sub Load_Config_Info()
Dim rs As Recordset
    Set rs = New Recordset
 
    rs.Open "select LASTUPDATE as maxdat from CONFIG", CurrentProject.Connection
    LastRDRUpdate = FormatDateTime(rs.Fields("maxdat").Value, vbShortDate)
 
    rs.Close
    rs.Open "select prevrdrupdt from config", CurrentProject.Connection
    PrevRDRUpdate = Format(rs.Fields("prevrdrupdt").Value, "mm/dd/yyyy")
    rs.Close
 
    If PrevRDRUpdate = #12:00:00 AM# Then PrevRDRUpdate = #11/9/2006#
    ThisSDate = LastRDRUpdate
    ThisEDate = LastRDRUpdate
 
    Set rs = Nothing
End Sub
 
 

[missinglinq]

I'm a little confused about a couple of things.

What do you mean by "doesn't display?" Nothing happens, or none of your controls show up?

What's the Record Source of the form?

Obviously the Record Source is present in the second form. Is it present and does it actually have data in it in the first database?

[ewart strain]

The actual form which allows me to view/add/delete info does not show up. A blank image is display.

The record source is a table in access, all data are present

I'm a little confused about a couple of things.

What do you mean by "doesn't display?" Nothing happens, or none of your controls show up?

What's the Record Source of the form?

Obviously the Record Source is present in the second form. Is it present and does it actually have data in it in the first database?

[ewart strain]

Here's the code for the form:

Option Compare Database
Option Explicit
 
 
Private Sub cmdAdd_Click()
cmdSave.Visible = True
cmdCXL.Visible = True
cmdNewBase.Visible = False
cmdOpenUpdt.Visible = False
Form_frmRELO570.AllowAdditions = True
Form_frmRELO570.DataEntry = True
Form_RELO570.Visible = False
 
End Sub
 
Private Sub cmdCXL_Click()
Form.Undo
cmdSave.Visible = False
cmdNewBase.Visible = True
cmdOpenUpdt.Visible = True
Form_frmRELO570.DataEntry = False
Form_frmRELO570.AllowAdditions = False
Form_RELO570.Visible = True
cmdCXL.Visible = False
End Sub
 
Private Sub cmdDel_Click()
Dim CURWKR As String
CURWKR = fullname
If MsgBox("ARE YOU SURE YOU WANT TO DELETE " & Chr(13) & "WORKER ID: " & WKR_ID & " - " & fullname, vbYesNo) = vbYes Then
 
Form_RELO570.RecordSource = ""
DoCmd.RunSQL "DELETE FROM RELO570 WHERE FULLNAME = '" & CURWKR & "'"
Form_RELO570.RecordSource = "RELO570"
End If
End Sub
 
Private Sub cmdEXIT_Click()
DoCmd.Close
End Sub
 
Private Sub cmdNewBase_Click()
RecordSource = ""
Form_RELO570.RecordSource = ""
DoCmd.RunSQL "SELECT * INTO RELO570ORIGIN" & Format(Now(), "YYYYMMDDHHMMSS") & " FROM RELO570ORIGIN"
DoCmd.RunSQL "SELECT * INTO RELO570ORIGIN FROM 570EXIT"
DoCmd.RunSQL "DELETE FROM RELO570 WHERE INSTR(COMMENTS,'Remove from move sheet.')<>0"
MsgBox "A new baseline for the excel spreadsheet has been created", vbOKOnly
Form_RELO570.RecordSource = "RELO570"
End Sub
 
Private Sub cmdOpenUpdt_Click()
Shell "excel.exe " & MyDefDir & "\570Relocation\GNS570to222M&TReport.xlt", vbMaximizedFocus
End Sub
 
Private Sub cmdSave_Click()
cmdCXL.Visible = False
cmdNewBase.Visible = True
cmdOpenUpdt.Visible = True
Form_frmRELO570.DataEntry = False
Form_frmRELO570.AllowAdditions = False
Form_RELO570.Visible = True
cmdSave.Visible = False
End Sub
 
Private Sub Form_Load()
Form_frmMain.Visible = False
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
Form_frmMain.Visible = True
End Sub
 

[MMcCarthy]

The actual form which allows me to view/add/delete info does not show up. A blank image is display.

The record source is a table in access, all data are present

Are you saying you get a blank empty form? This happens when there is not record to display.

[ewart strain]

Not a blank form but rather an image. In other words there's an outline of the form. It work fine a few day ago

[MMcCarthy]

Not a blank form but rather an image. In other words there's an outline of the form. It work fine a few day ago

When I say blank I mean no controls. Just a grey shape (or whatever background it had)

Open the form in design view and have a look at the Record source property of the form. If this is a table try opening the table directly. If it's a query try running the query.

[ewart strain]

Then you are correct in your assumption

[MMcCarthy]

Then you are correct in your assumption

Did you try opening the table or query behind the form as I suggested?

[ewart strain]

The table opens up fine

[MMcCarthy]

The table opens up fine

OK if you try to open the form directly and not from the other form what happens?

[ewart strain]

I encounter the same result

[MMcCarthy]

I encounter the same result

OK open the form in design view and go to properties.

1. As previously mentioned check that the Record Source under the Data tab is correct.
2. Check that Filter Property is not set
3. There are two forms referred to in your code frmRELO570 and frmMain. Are either of these the current form? If not what is the current forms name

Once you check these we can move forward.

[ewart strain]

Frm RELO570 is the form in questioned

[MMcCarthy]

Frm RELO570 is the form in questioned

OK leave it with me. I have to go out for a couple of hours. I'll look at it when I get back unless someone beats me to it.

[ewart strain]

This will be appreciated greatly

[missinglinq]

And just as an aside while we try to figure this out. It'll make life much easier for you as while as for us, anytime we try to help you, if you change how you refer to the current form in VBA code.

If the current form is named frmRELO570 and you are working in the VBE, which is to say the code window, behind that form, instead of writing, for instance

frmRELO570.AllowAdditions = True

simply write

Me.AllowAdditions = True

The Me is shorthand, if you will, for the current form's name. This can be used anytime you're referring to the form's properties! Referring to any other form from frmRELO570 you would, of course, still use the long method for that other form.

[FishVal]

Here's the code for the form:
Option Compare Database
Option Explicit


Private Sub cmdAdd_Click()
cmdSave.Visible = True
cmdCXL.Visible = True
cmdNewBase.Visible = False
cmdOpenUpdt.Visible = False
Form_frmRELO570.AllowAdditions = True
Form_frmRELO570.DataEntry = True
Form_RELO570.Visible = False

End Sub

Private Sub cmdCXL_Click()
Form.Undo
cmdSave.Visible = False
cmdNewBase.Visible = True
cmdOpenUpdt.Visible = True
Form_frmRELO570.DataEntry = False
Form_frmRELO570.AllowAdditions = False
Form_RELO570.Visible = True
cmdCXL.Visible = False
End Sub

Private Sub cmdDel_Click()
Dim CURWKR As String
CURWKR = fullname
If MsgBox("ARE YOU SURE YOU WANT TO DELETE " & Chr(13) & "WORKER ID: " & WKR_ID & " - " & fullname, vbYesNo) = vbYes Then

Form_RELO570.RecordSource = ""
DoCmd.RunSQL "DELETE FROM RELO570 WHERE FULLNAME = '" & CURWKR & "'"
Form_RELO570.RecordSource = "RELO570"
End If
End Sub

Private Sub cmdEXIT_Click()
DoCmd.Close
End Sub

Private Sub cmdNewBase_Click()
RecordSource = ""
Form_RELO570.RecordSource = ""
DoCmd.RunSQL "SELECT * INTO RELO570ORIGIN" & Format(Now(), "YYYYMMDDHHMMSS") & " FROM RELO570ORIGIN"
DoCmd.RunSQL "SELECT * INTO RELO570ORIGIN FROM 570EXIT"
DoCmd.RunSQL "DELETE FROM RELO570 WHERE INSTR(COMMENTS,'Remove from move sheet.')<>0"
MsgBox "A new baseline for the excel spreadsheet has been created", vbOKOnly
Form_RELO570.RecordSource = "RELO570"
End Sub

Private Sub cmdOpenUpdt_Click()
Shell "excel.exe " & MyDefDir & "\570Relocation\GNS570to222M&TReport.xlt", vbMaximizedFocus
End Sub

Private Sub cmdSave_Click()
cmdCXL.Visible = False
cmdNewBase.Visible = True
cmdOpenUpdt.Visible = True
Form_frmRELO570.DataEntry = False
Form_frmRELO570.AllowAdditions = False
Form_RELO570.Visible = True
cmdSave.Visible = False
End Sub

Private Sub Form_Load()
Form_frmMain.Visible = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
Form_frmMain.Visible = True
End Sub

Could you clarify how many forms actually are involved in discussion.
1) You have frmMain which visibility depends on whether the form with code above loaded or unloaded.
2) You have a form the code of which you have provided. Plz, provide its name and the way it is being opened (from frmMain to my mind).
3) The code above deals with frmRELO570 and RELO570 forms in a really bizzare way. What it actually supposed to do?

 
Private Sub cmdAdd_Click()
    cmdSave.Visible = True
    cmdCXL.Visible = True
    cmdNewBase.Visible = False
    cmdOpenUpdt.Visible = False
    Form_frmRELO570.AllowAdditions = True
    Form_frmRELO570.DataEntry = True
    Form_RELO570.Visible = False
 
End Sub
 

4) In msg#3 you've provided Form_Load event handler code. To what form does it relate?

Will be glad to help you when you will clarify me forms relationships.

[MMcCarthy]

I have edited the code slightly including commenting out some lines that made the form visible and invisible. After adding this code, open the form in design view and make sure the visible property is set to yes.

Option Compare Database
Option Explicit 
 
Private Sub cmdAdd_Click()
    cmdSave.Visible = True
    cmdCXL.Visible = True
    cmdNewBase.Visible = False
    cmdOpenUpdt.Visible = False
    Me.AllowAdditions = True
    Me.DataEntry = True
    'Me.Visible = False
End Sub
Private Sub cmdCXL_Click()
    Me.Undo
    cmdSave.Visible = False
    cmdNewBase.Visible = True
    cmdOpenUpdt.Visible = True
    Me.DataEntry = False
    Me.AllowAdditions = False
    'Me.Visible = True
    cmdCXL.Visible = False
End Sub
Private Sub cmdDel_Click()
Dim CURWKR As String
    CURWKR = FullName
    If MsgBox("ARE YOU SURE YOU WANT TO DELETE " & Chr(13) & "WORKER ID: " & WKR_ID & " - " & FullName, vbYesNo) = vbYes Then
        Me.RecordSource = ""
        DoCmd.RunSQL "DELETE FROM RELO570 WHERE FULLNAME = '" & CURWKR & "'"
        Me.RecordSource = "RELO570"
    End If
End Sub
Private Sub cmdEXIT_Click()
    DoCmd.Close
End Sub
Private Sub cmdNewBase_Click()
    Me.RecordSource = ""
    DoCmd.RunSQL "SELECT * INTO RELO570ORIGIN" & Format(Now(), "YYYYMMDDHHMMSS") & " FROM RELO570ORIGIN"
    DoCmd.RunSQL "SELECT * INTO RELO570ORIGIN FROM 570EXIT"
    DoCmd.RunSQL "DELETE FROM RELO570 WHERE INSTR(COMMENTS,'Remove from move sheet.')<>0"
    MsgBox "A new baseline for the excel spreadsheet has been created", vbOKOnly
    Me.RecordSource = "RELO570"
End Sub
Private Sub cmdOpenUpdt_Click()
    Shell "excel.exe " & MyDefDir & "\570Relocation\GNS570to222M&TReport.xlt", vbMaximizedFocus
End Sub
Private Sub cmdSave_Click()
    cmdCXL.Visible = False
    cmdNewBase.Visible = True
    cmdOpenUpdt.Visible = True
    Me.DataEntry = False
    Me.AllowAdditions = False
    'Me.Visible = True
    cmdSave.Visible = False
End Sub
Private Sub Form_Load()
    Forms!frmMain.Visible = False
End Sub
Private Sub Form_Unload(Cancel As Integer)
    Forms!frmMain.Visible = True
End Sub
 
 

[ewart strain]

Thx greatly, this is some good stuff. how can someone like me with no prior knowledge of VB learn relatively quickly?

[MMcCarthy]

Thx greatly, this is some good stuff. how can someone like me with no prior knowledge of VB learn relatively quickly?

Unfortunately, it's not really a quickly learned language. You can pick up some basics fairly easily by using the command button wizard and examining the code created and convert existing macros to VBA and examine how VBA handles the macro functions.

There is also a number of tutorials on various topics in the Articles section of this site. There is an indexed list of them here. Opening the help file from the VBA editor window will also give you an extensive help section.

Mary