Regarding Forms and Forms representing Related Table Data

[magmike]

I have a main form (Contacts) which has many related tables. For this
message we will use the related table "Letters" which stores records
representing different letters and literature sent to a contact.

When a user on the Contact form, presses the "Letters" button, they
will see the Letter records related to the contact currently
possessing the contents of the Contact form. With navigation buttons
in place, the user can browse through all the correspondence sent to
the contact in question. However, when pressed, the New Records button
(Titled SendLetter) starts an altogether new record without the
contact id in place, requiring the user to purposely include the
information on the company in the new record. The goal, is that the
information on the contact the new letter will be related to, is
already filled in.

How can I do this?

thanks in advance!

 

[Guest]

Is the letter form a different form to the client form, and is the client
form still open when the letter form is open? If so you can use the default
value of the letter client screen to display the existing client. It is best
to use the build function to ensure no typos. Go to the field (e.g. Client
Name) on the letter form, Click the data tab, go down to default and right
click. Select Build and find the value you want from the other open form.

 

[magmike]

Is the letter form a different form to the client form, and is the client
form still open when the letter form is open? If so you can use the default
value of the letter client screen to display the existing client. It is best
to use the build function to ensure no typos. Go to the field (e.g. Client
Name) on the letter form, Click the data tab, go down to default and right
click. Select Build and find the value you want from the other open form.








- Show quoted text -

Awesome, thanks! Is there a way to only have that default value set,
if the NewRecord button is pushed?

 

[magmike]

Is the letter form a different form to the client form, and is the client
form still open when the letter form is open? If so you can use the default
value of the letter client screen to display the existing client. It is best
to use the build function to ensure no typos. Go to the field (e.g. Client
Name) on the letter form, Click the data tab, go down to default and right
click. Select Build and find the value you want from the other open form.








- Show quoted text -

Or... is there a way to hide the form feilds if there are not any
current records (or letters) and only show a message saying there have
not been any letters sent, and a button that says, Send Letter Now,
that when pushed reveals the form, with default company id entered?

Thanks!

 

[magmike]

Is the letter form a different form to the client form, and is the client
form still open when the letter form is open? If so you can use the default
value of the letter client screen to display the existing client. It is best
to use the build function to ensure no typos. Go to the field (e.g. Client
Name) on the letter form, Click the data tab, go down to default and right
click. Select Build and find the value you want from the other open form.








- Show quoted text -

Awesome, thanks! And actually, since I am using a "new record" button,
I set the on click as such:

Private Sub AddNewCall_Click()
On Error GoTo Err_AddNewCall_Click


DoCmd.GoToRecord , , acNewRec
Forms!CallsForm!Combo26.Value = [Forms]![ProspectForm]![ID]

Exit_AddNewCall_Click:
Exit Sub

Err_AddNewCall_Click:
MsgBox Err.Description
Resume Exit_AddNewCall_Click

End Sub

HOWEVER, would it be possible to have the form hide all fields if
there are not records matching criteria, or show a field that hides
all others and simply says: "There are no records" with a button to
add a new one?

Does that make sense?

magmike

 

[Guest]

Hi

A couple of things. Firstly you can use a conditional statement and use the
visible property of the form. Secondly for a message, you can either use a
label on the form that is hidden normally and you display it when
appropriate, or you can use a pop up message box.

There are a couple of ways to check if there are records. One is to do a
record count and if it is zero, fire up the message and hide the fields.
Once again I use a generic function to count records. I just pass the SQL
statement and it returns the number of records. You could use this for the
On Current event.

Dim strSQL as String

strSQL = "SELECT * from tblLetters where tblLetters.ClientNo = " &
Forms!frmClient!ClientNo

If funRecordCount (strSQL)>0 then ' Records exist
Me!txtClientName.Visible = True ' Add any fields you want to make
visible
Me!lblCustomWarning.Visible = False ' Hide the warning message
Else
Me!txtClientName.Visible = False ' Hide any fields
Me!lblCustomWarning.Visible = True ' Display the warning
End If

The function for funRecordCount can be stored in a module.

Public Function funRecordCount(strSQL As String) As Integer
Dim dbs As Database
Dim rst As Recordset

On Error GoTo Error_funRecordCount

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

' Find the number of records. First test records were found.
If (rst.EOF = True) And (rst.BOF = True) Then
funRecordCount = 0 ' No
records found
Else
rst.MoveLast ' End of
the recordset
funRecordCount = rst.RecordCount ' Number
of records
End If

Exit_funRecordCount:
On Error GoTo 0
Set dbs = Nothing
Set rst = Nothing
Exit Function

Error_funRecordCount:
MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: modGeneric" & funCrLf & _
"Type: Module" & funCrLf & _
"Calling Procedure: funRecordCount" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_funRecordCount

End Function

Is the letter form a different form to the client form, and is the client
form still open when the letter form is open? If so you can use the default
value of the letter client screen to display the existing client. It is best
to use the build function to ensure no typos. Go to the field (e.g. Client
Name) on the letter form, Click the data tab, go down to default and right
click. Select Build and find the value you want from the other open form.








- Show quoted text -

Awesome, thanks! And actually, since I am using a "new record" button,
I set the on click as such:

Private Sub AddNewCall_Click()
On Error GoTo Err_AddNewCall_Click


DoCmd.GoToRecord , , acNewRec
Forms!CallsForm!Combo26.Value = [Forms]![ProspectForm]![ID]

Exit_AddNewCall_Click:
Exit Sub

Err_AddNewCall_Click:
MsgBox Err.Description
Resume Exit_AddNewCall_Click

End Sub

HOWEVER, would it be possible to have the form hide all fields if
there are not records matching criteria, or show a field that hides
all others and simply says: "There are no records" with a button to
add a new one?

Does that make sense?

magmike

 

[magmike]

Hi

A couple of things. Firstly you can use a conditional statement and use the
visible property of the form. Secondly for a message, you can either use a
label on the form that is hidden normally and you display it when
appropriate, or you can use a pop up message box.

There are a couple of ways to check if there are records. One is to do a
record count and if it is zero, fire up the message and hide the fields.
Once again I use a generic function to count records. I just pass the SQL
statement and it returns the number of records. You could use this for the
On Current event.

Dim strSQL as String

strSQL = "SELECT * from tblLetters where tblLetters.ClientNo = " &
Forms!frmClient!ClientNo

If funRecordCount (strSQL)>0 then ' Records exist
Me!txtClientName.Visible = True ' Add any fields you want to make
visible
Me!lblCustomWarning.Visible = False ' Hide the warning message
Else
Me!txtClientName.Visible = False ' Hide any fields
Me!lblCustomWarning.Visible = True ' Display the warning
End If

The function for funRecordCount can be stored in a module.

Public Function funRecordCount(strSQL As String) As Integer
Dim dbs As Database
Dim rst As Recordset

On Error GoTo Error_funRecordCount

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

' Find the number of records. First test records were found.
If (rst.EOF = True) And (rst.BOF = True) Then
funRecordCount = 0 ' No
records found
Else
rst.MoveLast ' End of
the recordset
funRecordCount = rst.RecordCount ' Number
of records
End If

Exit_funRecordCount:
On Error GoTo 0
Set dbs = Nothing
Set rst = Nothing
Exit Function

Error_funRecordCount:
MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: modGeneric" & funCrLf & _
"Type: Module" & funCrLf & _
"Calling Procedure: funRecordCount" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_funRecordCount

End Function

Awesome, thanks! And actually, since I am using a "new record" button,
I set the on click as such:

Private Sub AddNewCall_Click()
On Error GoTo Err_AddNewCall_Click

DoCmd.GoToRecord , , acNewRec
Forms!CallsForm!Combo26.Value = [Forms]![ProspectForm]![ID]

Exit_AddNewCall_Click:
Exit Sub

Err_AddNewCall_Click:
MsgBox Err.Description
Resume Exit_AddNewCall_Click

End Sub

HOWEVER, would it be possible to have the form hide all fields if
there are not records matching criteria, or show a field that hides
all others and simply says: "There are no records" with a button to
add a new one?

Does that make sense?

magmike- Hide quoted text -

- Show quoted text -

I am trying to implement this. However, now, when I open the letters
form I get the following error:

Compile Error:

User-defined type not defined.

While this message is displayed, the debugger comes up and is
highlighting "dbs as Database" in the code where Dim was in front of
it. Have I left something out?

thanks!

 

[Guest]

Sounds like references. In the VBA screen go to Tools, References and make
sure the Microsoft DAO 3.6 Object Library is ticked and near the top (Usually
around row 3). Depending on your version, you might have a different number
to 3.6.

Hope that helps

Hi

A couple of things. Firstly you can use a conditional statement and use the
visible property of the form. Secondly for a message, you can either use a
label on the form that is hidden normally and you display it when
appropriate, or you can use a pop up message box.

There are a couple of ways to check if there are records. One is to do a
record count and if it is zero, fire up the message and hide the fields.
Once again I use a generic function to count records. I just pass the SQL
statement and it returns the number of records. You could use this for the
On Current event.

Dim strSQL as String

strSQL = "SELECT * from tblLetters where tblLetters.ClientNo = " &
Forms!frmClient!ClientNo

If funRecordCount (strSQL)>0 then ' Records exist
Me!txtClientName.Visible = True ' Add any fields you want to make
visible
Me!lblCustomWarning.Visible = False ' Hide the warning message
Else
Me!txtClientName.Visible = False ' Hide any fields
Me!lblCustomWarning.Visible = True ' Display the warning
End If

The function for funRecordCount can be stored in a module.

Public Function funRecordCount(strSQL As String) As Integer
Dim dbs As Database
Dim rst As Recordset

On Error GoTo Error_funRecordCount

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

' Find the number of records. First test records were found.
If (rst.EOF = True) And (rst.BOF = True) Then
funRecordCount = 0 ' No
records found
Else
rst.MoveLast ' End of
the recordset
funRecordCount = rst.RecordCount ' Number
of records
End If

Exit_funRecordCount:
On Error GoTo 0
Set dbs = Nothing
Set rst = Nothing
Exit Function

Error_funRecordCount:
MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: modGeneric" & funCrLf & _
"Type: Module" & funCrLf & _
"Calling Procedure: funRecordCount" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_funRecordCount

End Function

On Oct 30, 1:43 am, NevilleT <[email protected]>
wrote:
Is the letter form a different form to the client form, and is the client
form still open when the letter form is open? If so you can use the default
value of the letter client screen to display the existing client. It is best
to use the build function to ensure no typos. Go to the field (e.g. Client
Name) on the letter form, Click the data tab, go down to default and right
click. Select Build and find the value you want from the other open form.

:
I have a main form (Contacts) which has many related tables. For this
message we will use the related table "Letters" which stores records
representing different letters and literature sent to a contact.

When a user on the Contact form, presses the "Letters" button, they
will see the Letter records related to the contact currently
possessing the contents of the Contact form. With navigation buttons
in place, the user can browse through all the correspondence sent to
the contact in question. However, when pressed, the New Records button
(Titled SendLetter) starts an altogether new record without the
contact id in place, requiring the user to purposely include the
information on the company in the new record. The goal, is that the
information on the contact the new letter will be related to, is
already filled in.

How can I do this?

thanks in advance!- Hide quoted text -

- Show quoted text -

Awesome, thanks! And actually, since I am using a "new record" button,
I set the on click as such:

Private Sub AddNewCall_Click()
On Error GoTo Err_AddNewCall_Click

DoCmd.GoToRecord , , acNewRec
Forms!CallsForm!Combo26.Value = [Forms]![ProspectForm]![ID]

Exit_AddNewCall_Click:
Exit Sub

Err_AddNewCall_Click:
MsgBox Err.Description
Resume Exit_AddNewCall_Click

End Sub

HOWEVER, would it be possible to have the form hide all fields if
there are not records matching criteria, or show a field that hides
all others and simply says: "There are no records" with a button to
add a new one?

Does that make sense?

magmike- Hide quoted text -

- Show quoted text -

I am trying to implement this. However, now, when I open the letters
form I get the following error:

Compile Error:

User-defined type not defined.

While this message is displayed, the debugger comes up and is
highlighting "dbs as Database" in the code where Dim was in front of
it. Have I left something out?

thanks!