Newbie: Can't make my database work

[Ed from AZ]

I'm pretty decent with Word and Excel VBA, but I've never really tried
an Access DB from scratch before. I started to build one with the
help of smoe gurus here at work, so naturally they gave me stuff
that's well over my head. It's been put aside for a few months, but
now I need to get it going. It was originally begun in Access 2003,
but since then we've been upgraded to 2007.

Last night I dusted this thing off and actually was able to open the
main form, click a button, and get my data entry form to open up. But
I was getting some errors when I tried to load my data, so I closed
it. This morning, I can get the main form to open, but now it throws
an error trying to open my data entry form.

In the main screen, I double-click frmMainScreen and it opens. I
click btnMainAddEdit which has the following code:

Private Sub btnMainAddEdit_Click()
Load frmDataEntry
DoEvents
frmDataEntry.Visible = True
frmMainScreen.Visible = False
End Sub

It errors on the Load statement with "Object is required". When I
mouse over frmDataEntry, it says "Empty"??? (Yes, there is a form
"frmDataEntry" in the list of Access Objects.)

How do I correctly refer to another form in the same project and load
it?

Ed

 

[Douglas J. Steele]

Private Sub btnMainAddEdit_Click()
Load frmDataEntry
DoEvents
Forms!frmDataEntry.Visible = True
Forms!frmMainScreen.Visible = False
End Sub

 

[Dirk Goldgar]

I'm pretty decent with Word and Excel VBA, but I've never really tried
an Access DB from scratch before. I started to build one with the
help of smoe gurus here at work, so naturally they gave me stuff
that's well over my head. It's been put aside for a few months, but
now I need to get it going. It was originally begun in Access 2003,
but since then we've been upgraded to 2007.

Last night I dusted this thing off and actually was able to open the
main form, click a button, and get my data entry form to open up. But
I was getting some errors when I tried to load my data, so I closed
it. This morning, I can get the main form to open, but now it throws
an error trying to open my data entry form.

In the main screen, I double-click frmMainScreen and it opens. I
click btnMainAddEdit which has the following code:

Private Sub btnMainAddEdit_Click()
Load frmDataEntry
DoEvents
frmDataEntry.Visible = True
frmMainScreen.Visible = False
End Sub

It errors on the Load statement with "Object is required". When I
mouse over frmDataEntry, it says "Empty"??? (Yes, there is a form
"frmDataEntry" in the list of Access Objects.)

How do I correctly refer to another form in the same project and load
it?

Try this:

Private Sub btnMainAddEdit_Click()

DoCmd.OpenForm "frmDataEntry"
Me.Visible = False

End Sub

That's assuming frmMainScreen is the current form, the one containing
btnMainAddEdit, and that you really want to *hide* that form, not close it.
If you wanted to close it, you'd write

DoCmd.Close acForm, Me.Name, acSaveNo

 

[Ed from AZ]

Thanks for responding, Doug, but sorry - it still errored out on the
Load statement. Dirk's method worked, though.

Ed

 

[Ed from AZ]

Thank you, Dirk. The DoCmd worked great.

Ed

Try this:

    Private Sub btnMainAddEdit_Click()

        DoCmd.OpenForm "frmDataEntry"
        Me.Visible = False

    End Sub

That's assuming frmMainScreen is the current form, the one containing
btnMainAddEdit, and that you really want to *hide* that form, not close it..
If you wanted to close it, you'd write

    DoCmd.Close acForm, Me.Name, acSaveNo

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

 

[Douglas J. Steele]

Geeze, I wasn't thinking, was I? "Load" is VB syntax, not Access.

Glad Dirk was around to straighten us out!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for responding, Doug, but sorry - it still errored out on the
Load statement. Dirk's method worked, though.

Ed

 

[Ed from AZ]

Now that the main form opens and loads, it's on to the next issue!

Last night I dusted this thing off and actually was able to open the
main form, click a button, and get my data entry form to open up.  But
I was getting some errors when I tried to load my data

As I said, I built this using input from those of much greater
understanding than I, so I'm sorry if these errors are obvious.

When I enter my parameters in frmDataEntry and click the "Add New"
button, the code sets an ADODB connection to a table in the database,
collects the inputs I've just typed and enters them as a new
Recordset. That works - the information is entered.

Then the code moves down to use a function to define a RecordSource
and do a Requery. (I think so anyway - this is what the code that I
was given says.) The RecordSource line uses a function to build a
variable from the form inputs.

Everything works great - until it gets ready to complete the code line
that calls the function. The code steps to the funtion line, runs
through the function, returns to the function - and then starts
calling for the textbox values from another form that isn't called
for! If I cancel the InputBoxes calling for the values, the code
stops because I cancelled an operation. If I input values, I have to
keep re-inputting the values until the form shows with errors, and
then the code errors out because I've got errors!

Here's the code. If y'all can help in any way, I'd appreciate it.
I'm about ready to go back to spreadsheets!

Ed

Private Sub btnAddPart_Click()

On Error GoTo Err_btnAddPart_Click
Stop

Dim dbConn As ADODB.Connection
Dim recSet As ADODB.Recordset

Set dbConn = CurrentProject.Connection
Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseServer
recSet.CursorType = adOpenKeyset
recSet.LockType = adLockOptimistic
recSet.Open "[tabPartsInstl]", dbConn, , , adCmdTable

recSet.AddNew ' Start a new record
recSet!Vehicle_SN = Me.Vehicle_SN.Value
recSet!Part_Code = Me.Part_Code.Value
recSet!PartSN = Me.PartSN
recSet!Position = Me.Position
recSet!InstDate = Me.InstDate

recSet.Update ' Post the new record
recSet.Close

Set recSet = Nothing
dbConn.Close

'********** ERRORS ON NEXT LINE ********************
' The code will hit this line, jump into the BuildFilter function,
then return here.
' Then I get InputBoxes asking for values from another form.
Me.frmPartsInstall.Form.RecordSource = "SELECT * FROM qryPartsInstl "
& BuildFilter

Me.frmPartsInstall.Requery

DoCmd.MoveSize , , 13500, 8500
DoCmd.Save

Exit_btnAddPart_Click:
Exit Sub

Err_btnAddPart_Click:
MsgBox Err.Description
Resume Exit_btnAddPart_Click

End Sub

Private Function BuildFilter() As Variant

Dim varFindThis As Variant

varFindThis = Null
varFindThis = "WHERE "

'Get values from form
If Me.Vehicle_SN.Value <> "" Then
varFindThis = varFindThis & "[Vehicle_SN] = " & Chr(34) &
Me.Vehicle_SN.Value & Chr(34) & " AND "
Else
MsgBox "Please select a Vehicle."
varFindThis = Null
Exit Function
End If

If Me.Part_Code.Value <> "" Then
varFindThis = varFindThis & "[Part_Code] = " & Chr(34) &
Me.Part_Code.Value & Chr(34) & " AND "
Else
MsgBox "Please select a Part Code."
varFindThis = Null
Exit Function
End If

If Me.PartSN <> "" Then
varFindThis = varFindThis & "[PartSN] = " & Chr(34) & Me.PartSN &
Chr(34) & " AND "
Else
MsgBox "Please enter a Part Serial Number or NA."
varFindThis = Null
Exit Function
End If

If Me.Position <> "" Then
varFindThis = varFindThis & "[Position] = " & Chr(34) &
Me.Position & Chr(34) & " AND "
Else
MsgBox "Please enter a Part Location."
varFindThis = Null
Exit Function
End If

If Me.InstDate <> "" Then
varFindThis = varFindThis & "[InstDate] = #" & Me.InstDate & "#
AND "
Else
MsgBox "Please enter the date the Part was installed."
varFindThis = Null
Exit Function
End If

'Add default value for RemvDate to filter
varFindThis = varFindThis & "[RemvDate] = #1/1/1900#"

Debug.Print varFindThis

BuildFilter = varFindThis

End Function

 

[Ed from AZ]

Just to close this out, I found a solution. If I open the form that
holds the missing variables just before the line that causes the
error, and then close it again immediaitely after, all runs smooth.

Ed

Now that the main form opens and loads, it's on to the next issue!

Last night I dusted this thing off and actually was able to open the
main form, click a button, and get my data entry form to open up.  But
I was getting some errors when I tried to load my data

As I said, I built this using input from those of much greater
understanding than I, so I'm sorry if these errors are obvious.

When I enter my parameters in frmDataEntry and click the "Add New"
button,  the code sets an ADODB connection to a table in the database,
collects the inputs I've just typed and enters them as a new
Recordset.  That works - the information is entered.

Then the code moves down to use a function to define a RecordSource
and do a Requery.  (I think so anyway - this is what the code that I
was given says.)  The RecordSource line uses a function to build a
variable from the form inputs.

Everything works great - until it gets ready to complete the code line
that calls the function.  The code steps to the funtion line, runs
through the function, returns to the function - and then starts
calling for the textbox values from another form that isn't called
for!  If I cancel the InputBoxes calling for the values, the code
stops because I cancelled an operation.  If I input values, I have to
keep re-inputting the values until the form shows with errors, and
then the code errors out because I've got errors!

Here's the code.  If y'all can help in any way, I'd appreciate it.
I'm about ready to go back to spreadsheets!

Ed

Private Sub btnAddPart_Click()

On Error GoTo Err_btnAddPart_Click
Stop

  Dim dbConn As ADODB.Connection
  Dim recSet As ADODB.Recordset

  Set dbConn = CurrentProject.Connection
  Set recSet = New ADODB.Recordset
  recSet.CursorLocation = adUseServer
  recSet.CursorType = adOpenKeyset
  recSet.LockType = adLockOptimistic
  recSet.Open "[tabPartsInstl]", dbConn, , , adCmdTable

  recSet.AddNew ' Start a new record
  recSet!Vehicle_SN = Me.Vehicle_SN.Value
  recSet!Part_Code = Me.Part_Code.Value
  recSet!PartSN = Me.PartSN
  recSet!Position = Me.Position
  recSet!InstDate = Me.InstDate

  recSet.Update ' Post the new record
  recSet.Close

  Set recSet = Nothing
  dbConn.Close

'********** ERRORS ON NEXT LINE ********************
' The code will hit this line, jump into the BuildFilter function,
then return here.
' Then I get InputBoxes asking for values from another form.
Me.frmPartsInstall.Form.RecordSource = "SELECT * FROM qryPartsInstl "
& BuildFilter

Me.frmPartsInstall.Requery

DoCmd.MoveSize , , 13500, 8500
DoCmd.Save

Exit_btnAddPart_Click:
    Exit Sub

Err_btnAddPart_Click:
    MsgBox Err.Description
    Resume Exit_btnAddPart_Click

End Sub

Private Function BuildFilter() As Variant

  Dim varFindThis As Variant

  varFindThis = Null
  varFindThis = "WHERE "

  'Get values from form
  If Me.Vehicle_SN.Value <> "" Then
    varFindThis = varFindThis & "[Vehicle_SN] = " & Chr(34) &
Me.Vehicle_SN.Value & Chr(34) & " AND "
  Else
    MsgBox "Please select a Vehicle."
    varFindThis = Null
    Exit Function
  End If

  If Me.Part_Code.Value <> "" Then
    varFindThis = varFindThis & "[Part_Code] = " & Chr(34) &
Me.Part_Code.Value & Chr(34) & " AND "
  Else
    MsgBox "Please select a Part Code."
    varFindThis = Null
    Exit Function
  End If

  If Me.PartSN <> "" Then
    varFindThis = varFindThis & "[PartSN] = " & Chr(34) & Me.PartSN &
Chr(34) & " AND "
  Else
    MsgBox "Please enter a Part Serial Number or NA."
    varFindThis = Null
    Exit Function
  End If

  If Me.Position <> "" Then
    varFindThis = varFindThis & "[Position] = " & Chr(34) &
Me.Position & Chr(34) & " AND "
  Else
    MsgBox "Please enter a Part Location."
    varFindThis = Null
    Exit Function
  End If

  If Me.InstDate <> "" Then
    varFindThis = varFindThis & "[InstDate] = #" & Me.InstDate & "#
AND "
  Else
    MsgBox "Please enter the date the Part was installed."
    varFindThis = Null
    Exit Function
  End If

  'Add default value for RemvDate to filter
  varFindThis = varFindThis & "[RemvDate] = #1/1/1900#"

  Debug.Print varFindThis

  BuildFilter = varFindThis

End Function