Finding Records from Sub-Form Table

[Guest]

Hi All,
I have searched the groups for an answer but can't find it so far...
I have a form 'Customers' and it has an embedded? Sub-Form 'Product'.
I have a Find button - via the cmd button wizard which brings up the Find &
Replace Dialog. Having selected the field in either the Customer or Product
forms I can indeed 'Find' a record. However, whilst the Customer Find is OK,
the Product Find will only let me find records relating to the selected
customer. What I need is a Find that will allow me to search for records in
the Product Table and bring up the Customer Record and the corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find the relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere I can
learn how to do it. I am familiar with VBA it's just how you pull this info
together I can't work out. New to SQL etc.

Any help greatly rewarded with lots of thank you's.

Cheers

Chas

 

[Allen Browne]

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a match in
the subform (by assigning an INNER JOIN statement to its RecordSource), and
how to combine that technique with filters on the main form's fields at the
same time.

 

[Guest]

Thanks for the link Allen,
I had looked at this before when trawling through the groups. Unfortunately
I cannot seem to get it to work and for now am not too bothered as I'm still
learning and have ordered a couple of books on Access to learn more. (Access
2000 Bible & MS Access Step by step). Could you recommend a better one?

I have tried one of your earlier tips on searching and made that work. I
have Created a form which opens on clicking a button on the main form. Here
the user can enter a number for one of either two searches, I'll incorporate
more when I can.

Here is the code from the After Update event of one of the Text boxes which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record selected is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd line to
check if the value of SORMA within the table SORMA_Results is valid, i.e., it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then... and so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN Statement?

Is there a Reference File anywhere which lists this kind of information as I
cannot seem to find it in Access help.

Cheers

Chas

 

[Allen Browne]

I think you are trying to determine whether there are any records in the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better one?

I have tried one of your earlier tips on searching and made that work. I
have Created a form which opens on clicking a button on the main form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd line to
check if the value of SORMA within the table SORMA_Results is valid, i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then... and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN Statement?

Is there a Reference File anywhere which lists this kind of information as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a match
in
the subform (by assigning an INNER JOIN statement to its RecordSource),
and
how to combine that technique with filters on the main form's fields at
the
same time.

 

[Guest]

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the table, I
know there is, what I want to do is validate that the record with the number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g., 2244) I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is shown with
that value in the SORMA field box. If I enter a false value, a blank record
is shown with the value 0 in the SORMA field Box.
--
Cheers
Chas

***************
* Spectrum is Green *
***************

I think you are trying to determine whether there are any records in the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better one?

I have tried one of your earlier tips on searching and made that work. I
have Created a form which opens on clicking a button on the main form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd line to
check if the value of SORMA within the table SORMA_Results is valid, i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then... and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN Statement?

Is there a Reference File anywhere which lists this kind of information as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a match
in
the subform (by assigning an INNER JOIN statement to its RecordSource),
and
how to combine that technique with filters on the main form's fields at
the
same time.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi All,
I have searched the groups for an answer but can't find it so far...
I have a form 'Customers' and it has an embedded? Sub-Form 'Product'.
I have a Find button - via the cmd button wizard which brings up the
Find
&
Replace Dialog. Having selected the field in either the Customer or
Product
forms I can indeed 'Find' a record. However, whilst the Customer Find
is
OK,
the Product Find will only let me find records relating to the selected
customer. What I need is a Find that will allow me to search for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere I can
learn how to do it. I am familiar with VBA it's just how you pull this
info
together I can't work out. New to SQL etc.

Any help greatly rewarded with lots of thank you's.

Cheers

Chas

 

[Guest]

Hi Again Allen,

I tried your last check of total records and got an error. I then realised I
had named the Form with the same name as the table so I changed it to
Sorma_Records_Edit. Here is the revised code for the text box. This works OK.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If Forms!Sorma_Records_Edit.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If
DoCmd.OpenForm "Sorma_Records_Edit", , , strWhere
End If
End Sub

How can I change the line

If Forms!Sorma_Records_Edit.RecordsetClone.RecordCount > 0 Then

to something like

If NOT Forms!Sorma_Records_Edit.RecordsetClone. SORMA > 0 Then

to validate the entry against the values in the table so that the ZERO
record is not shown.

--
Cheers
Chas

***************
* Spectrum is Green *
***************

I think you are trying to determine whether there are any records in the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better one?

I have tried one of your earlier tips on searching and made that work. I
have Created a form which opens on clicking a button on the main form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd line to
check if the value of SORMA within the table SORMA_Results is valid, i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then... and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN Statement?

Is there a Reference File anywhere which lists this kind of information as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a match
in
the subform (by assigning an INNER JOIN statement to its RecordSource),
and
how to combine that technique with filters on the main form's fields at
the
same time.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi All,
I have searched the groups for an answer but can't find it so far...
I have a form 'Customers' and it has an embedded? Sub-Form 'Product'.
I have a Find button - via the cmd button wizard which brings up the
Find
&
Replace Dialog. Having selected the field in either the Customer or
Product
forms I can indeed 'Find' a record. However, whilst the Customer Find
is
OK,
the Product Find will only let me find records relating to the selected
customer. What I need is a Find that will allow me to search for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere I can
learn how to do it. I am familiar with VBA it's just how you pull this
info
together I can't work out. New to SQL etc.

Any help greatly rewarded with lots of thank you's.

Cheers

Chas

 

[Allen Browne]

DLookup() would be the simplest way to see if the number is in the table. It
will return Null if the entry is not found.

Use the BeforeUpdate event of you want to keep the user in the box until
they enter something valid.

Private Sub txtSearchSorma_BeforeUpdate(Cancel As Integer)
Dim strWhere As String

If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If IsNull(DLookup("SORMA", "Table1", strWhere)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End With
End Sub

More info on DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the table, I
know there is, what I want to do is validate that the record with the
number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g., 2244)
I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is shown
with
that value in the SORMA field box. If I enter a false value, a blank
record
is shown with the value 0 in the SORMA field Box.
--

I think you are trying to determine whether there are any records in the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better one?

I have tried one of your earlier tips on searching and made that work.
I
have Created a form which opens on clicking a button on the main form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record
selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd line
to
check if the value of SORMA within the table SORMA_Results is valid,
i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then...
and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN Statement?

Is there a Reference File anywhere which lists this kind of information
as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green


:

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a
match
in
the subform (by assigning an INNER JOIN statement to its
RecordSource),
and
how to combine that technique with filters on the main form's fields
at
the
same time.

Hi All,
I have searched the groups for an answer but can't find it so far...
I have a form 'Customers' and it has an embedded? Sub-Form
'Product'.
I have a Find button - via the cmd button wizard which brings up the
Find
&
Replace Dialog. Having selected the field in either the Customer or
Product
forms I can indeed 'Find' a record. However, whilst the Customer
Find
is
OK,
the Product Find will only let me find records relating to the
selected
customer. What I need is a Find that will allow me to search for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere I
can
learn how to do it. I am familiar with VBA it's just how you pull
this
info
together I can't work out. New to SQL etc.

Any help greatly rewarded with lots of thank you's.

 

[Guest]

Dear Allen,
Many thanks for the code. I changed the End With to End If and it works like
a charm.

Many Many thanks for your help with this. I'll study your web page for sure.
--
Cheers
Chas

***************
* Spectrum is Green *
***************

DLookup() would be the simplest way to see if the number is in the table. It
will return Null if the entry is not found.

Use the BeforeUpdate event of you want to keep the user in the box until
they enter something valid.

Private Sub txtSearchSorma_BeforeUpdate(Cancel As Integer)
Dim strWhere As String

If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If IsNull(DLookup("SORMA", "Table1", strWhere)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End With
End Sub

More info on DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the table, I
know there is, what I want to do is validate that the record with the
number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g., 2244)
I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is shown
with
that value in the SORMA field box. If I enter a false value, a blank
record
is shown with the value 0 in the SORMA field Box.
--

I think you are trying to determine whether there are any records in the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better one?

I have tried one of your earlier tips on searching and made that work.
I
have Created a form which opens on clicking a button on the main form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record
selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd line
to
check if the value of SORMA within the table SORMA_Results is valid,
i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then...
and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN Statement?

Is there a Reference File anywhere which lists this kind of information
as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green


:

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a
match
in
the subform (by assigning an INNER JOIN statement to its
RecordSource),
and
how to combine that technique with filters on the main form's fields
at
the
same time.

Hi All,
I have searched the groups for an answer but can't find it so far...
I have a form 'Customers' and it has an embedded? Sub-Form
'Product'.
I have a Find button - via the cmd button wizard which brings up the
Find
&
Replace Dialog. Having selected the field in either the Customer or
Product
forms I can indeed 'Find' a record. However, whilst the Customer
Find
is
OK,
the Product Find will only let me find records relating to the
selected
customer. What I need is a Find that will allow me to search for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere I
can
learn how to do it. I am familiar with VBA it's just how you pull
this
info
together I can't work out. New to SQL etc.

Any help greatly rewarded with lots of thank you's.

 

[Guest]

Hi Allen, Me again.
I'm getting used to the dlookup and it's working well for me.
My question is can I lookup TWO attributes with it?
I want to lookup the Product Make and the RS number so that when I have the
Make it also gives me the RS Number which I can then use as the trigger for
the form Whereis attribute.

The reason for this is the old Subform dilemma. I have the Make displayed in
the subform but want the find to bring up the Main forms' record.

Here's what I've done so far...

Private Sub txtFindMake_AfterUpdate()
'Find a Make in the RS table and Display from the RSNO
Dim strWhereMake As String
Dim strFoundMake As String
Dim FoundRSNo As Integer

If Not IsNull(Me.txtFindMake) Then
strWhereMake = "[Make] = '" & Me.txtFindMake & "'"
strFoundMake = DLookup("Make", "RS_Results", strWhereMake)
MsgBox strFoundMake, vbOKCancel, "Here is the Make" ' This is just to
test the find

'Close this form
DoCmd.Close
'Make the RELEASE Button visible
Forms!sorma_records_edit.cmdReleaseFilter.Visible = True
'Open the Edit form at the appropriate record

DoCmd.OpenForm "Sorma_Records_Edit", , , strWhereMake

End If
End Sub

the DoCmd does not work as the Field Make is on the subform

What I want is the RS Number for that Make (there may be many occurrences of
this but I'll deal with that later) so that I can display the Main form from
the RS Number.

Hope that's clear.

I know it's getting complicated and I really need to go back to your
http://allenbrowne.com/ser-28.html
page and work on this but I just thought that the dlookup for multiple
attributes might be easier for now.

--
Cheers
Chas

***************
* Spectrum is Green *
***************

DLookup() would be the simplest way to see if the number is in the table. It
will return Null if the entry is not found.

Use the BeforeUpdate event of you want to keep the user in the box until
they enter something valid.

Private Sub txtSearchSorma_BeforeUpdate(Cancel As Integer)
Dim strWhere As String

If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If IsNull(DLookup("SORMA", "Table1", strWhere)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End With
End Sub

More info on DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the table, I
know there is, what I want to do is validate that the record with the
number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g., 2244)
I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is shown
with
that value in the SORMA field box. If I enter a false value, a blank
record
is shown with the value 0 in the SORMA field Box.
--

I think you are trying to determine whether there are any records in the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better one?

I have tried one of your earlier tips on searching and made that work.
I
have Created a form which opens on clicking a button on the main form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record
selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd line
to
check if the value of SORMA within the table SORMA_Results is valid,
i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then...
and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN Statement?

Is there a Reference File anywhere which lists this kind of information
as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green


:

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a
match
in
the subform (by assigning an INNER JOIN statement to its
RecordSource),
and
how to combine that technique with filters on the main form's fields
at
the
same time.

Hi All,
I have searched the groups for an answer but can't find it so far...
I have a form 'Customers' and it has an embedded? Sub-Form
'Product'.
I have a Find button - via the cmd button wizard which brings up the
Find
&
Replace Dialog. Having selected the field in either the Customer or
Product
forms I can indeed 'Find' a record. However, whilst the Customer
Find
is
OK,
the Product Find will only let me find records relating to the
selected
customer. What I need is a Find that will allow me to search for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere I
can
learn how to do it. I am familiar with VBA it's just how you pull
this
info
together I can't work out. New to SQL etc.

Any help greatly rewarded with lots of thank you's.

 

[Allen Browne]

Sure. You can put 2 conditions into strWhere, e.g.:
If Not (IsNull(Me.txtFindMake) Or IsNull(Me.RS)) Then
strWhereMake = "([Make] = '" & Me.txtFindMake & _
"') AND ([RS] = " & Me.RS & ")"

The Criteria argument of DLookup() is just like the WHERE clause in a query.
You can mock up a query, switch it to SQL View (View menu, from query
design), and Access will show you what your criteria string should look
like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, Me again.
I'm getting used to the dlookup and it's working well for me.
My question is can I lookup TWO attributes with it?
I want to lookup the Product Make and the RS number so that when I have
the
Make it also gives me the RS Number which I can then use as the trigger
for
the form Whereis attribute.

The reason for this is the old Subform dilemma. I have the Make displayed
in
the subform but want the find to bring up the Main forms' record.

Here's what I've done so far...

Private Sub txtFindMake_AfterUpdate()
'Find a Make in the RS table and Display from the RSNO
Dim strWhereMake As String
Dim strFoundMake As String
Dim FoundRSNo As Integer

If Not IsNull(Me.txtFindMake) Then
strWhereMake = "[Make] = '" & Me.txtFindMake & "'"
strFoundMake = DLookup("Make", "RS_Results", strWhereMake)
MsgBox strFoundMake, vbOKCancel, "Here is the Make" ' This is just to
test the find

'Close this form
DoCmd.Close
'Make the RELEASE Button visible
Forms!sorma_records_edit.cmdReleaseFilter.Visible = True
'Open the Edit form at the appropriate record

DoCmd.OpenForm "Sorma_Records_Edit", , , strWhereMake

End If
End Sub

the DoCmd does not work as the Field Make is on the subform

What I want is the RS Number for that Make (there may be many occurrences
of
this but I'll deal with that later) so that I can display the Main form
from
the RS Number.

Hope that's clear.

I know it's getting complicated and I really need to go back to your
http://allenbrowne.com/ser-28.html
page and work on this but I just thought that the dlookup for multiple
attributes might be easier for now.

--
Cheers
Chas

***************
* Spectrum is Green *
***************

DLookup() would be the simplest way to see if the number is in the table.
It
will return Null if the entry is not found.

Use the BeforeUpdate event of you want to keep the user in the box until
they enter something valid.

Private Sub txtSearchSorma_BeforeUpdate(Cancel As Integer)
Dim strWhere As String

If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If IsNull(DLookup("SORMA", "Table1", strWhere)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End With
End Sub

More info on DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the table,
I
know there is, what I want to do is validate that the record with the
number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g.,
2244)
I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is shown
with
that value in the SORMA field box. If I enter a false value, a blank
record
is shown with the value 0 in the SORMA field Box.
--

:

I think you are trying to determine whether there are any records in
the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as
I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better
one?

I have tried one of your earlier tips on searching and made that
work.
I
have Created a form which opens on clicking a button on the main
form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text
boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record
selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd
line
to
check if the value of SORMA within the table SORMA_Results is valid,
i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then...
and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN
Statement?

Is there a Reference File anywhere which lists this kind of
information
as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green


:

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a
match
in
the subform (by assigning an INNER JOIN statement to its
RecordSource),
and
how to combine that technique with filters on the main form's
fields
at
the
same time.

Hi All,
I have searched the groups for an answer but can't find it so
far...
I have a form 'Customers' and it has an embedded? Sub-Form
'Product'.
I have a Find button - via the cmd button wizard which brings up
the
Find
&
Replace Dialog. Having selected the field in either the Customer
or
Product
forms I can indeed 'Find' a record. However, whilst the Customer
Find
is
OK,
the Product Find will only let me find records relating to the
selected
customer. What I need is a Find that will allow me to search for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find
the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere
I
can
learn how to do it. I am familiar with VBA it's just how you pull
this
info
together I can't work out. New to SQL etc.

Any help greatly rewarded with lots of thank you's.

 

[Guest]

Hi Allen,
Thanks for the answer. I guess I'm not as clued up as I thought but I'm
getting there.

I have found that the record for Make can be selected if the Table/Query
used for the source contains it. So I now have a search form that I can
search for an RS Number or SORMA Number or Make or Model. So far so good.

The problem is I have to use a query as a source which uses fields from TWO
tables. however I can't edit the records. If the source for the form is a
table or a Query with only fields from one table then I can.

What I would like is to search one table for the Make - thats on the subform
and has a SORMA Number in it and but brings the record up for the other table
which also has the SORMA number in it.

So I would look up the Make and return the SORMA number then use this for
the Open Form --- Whereis command

So the dlookup searches for Make but returns SORMA number. Is this possible
without a query linking two tables or am I doing something basically wrong
with the whole setup?

--
Cheers
Chas

***************
* Spectrum is Green *
***************

Sure. You can put 2 conditions into strWhere, e.g.:
If Not (IsNull(Me.txtFindMake) Or IsNull(Me.RS)) Then
strWhereMake = "([Make] = '" & Me.txtFindMake & _
"') AND ([RS] = " & Me.RS & ")"

The Criteria argument of DLookup() is just like the WHERE clause in a query.
You can mock up a query, switch it to SQL View (View menu, from query
design), and Access will show you what your criteria string should look
like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, Me again.
I'm getting used to the dlookup and it's working well for me.
My question is can I lookup TWO attributes with it?
I want to lookup the Product Make and the RS number so that when I have
the
Make it also gives me the RS Number which I can then use as the trigger
for
the form Whereis attribute.

The reason for this is the old Subform dilemma. I have the Make displayed
in
the subform but want the find to bring up the Main forms' record.

Here's what I've done so far...

Private Sub txtFindMake_AfterUpdate()
'Find a Make in the RS table and Display from the RSNO
Dim strWhereMake As String
Dim strFoundMake As String
Dim FoundRSNo As Integer

If Not IsNull(Me.txtFindMake) Then
strWhereMake = "[Make] = '" & Me.txtFindMake & "'"
strFoundMake = DLookup("Make", "RS_Results", strWhereMake)
MsgBox strFoundMake, vbOKCancel, "Here is the Make" ' This is just to
test the find

'Close this form
DoCmd.Close
'Make the RELEASE Button visible
Forms!sorma_records_edit.cmdReleaseFilter.Visible = True
'Open the Edit form at the appropriate record

DoCmd.OpenForm "Sorma_Records_Edit", , , strWhereMake

End If
End Sub

the DoCmd does not work as the Field Make is on the subform

What I want is the RS Number for that Make (there may be many occurrences
of
this but I'll deal with that later) so that I can display the Main form
from
the RS Number.

Hope that's clear.

I know it's getting complicated and I really need to go back to your
http://allenbrowne.com/ser-28.html
page and work on this but I just thought that the dlookup for multiple
attributes might be easier for now.

--
Cheers
Chas

***************
* Spectrum is Green *
***************

DLookup() would be the simplest way to see if the number is in the table.
It
will return Null if the entry is not found.

Use the BeforeUpdate event of you want to keep the user in the box until
they enter something valid.

Private Sub txtSearchSorma_BeforeUpdate(Cancel As Integer)
Dim strWhere As String

If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If IsNull(DLookup("SORMA", "Table1", strWhere)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End With
End Sub

More info on DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the table,
I
know there is, what I want to do is validate that the record with the
number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g.,
2244)
I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is shown
with
that value in the SORMA field box. If I enter a false value, a blank
record
is shown with the value 0 in the SORMA field Box.
--

:

I think you are trying to determine whether there are any records in
the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered as
I'm
still
learning and have ordered a couple of books on Access to learn more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a better
one?

I have tried one of your earlier tips on searching and made that
work.
I
have Created a form which opens on clicking a button on the main
form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text
boxes
which
searches for a record field then opens the data form at that record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric field.
This works well if the record exists but if not then the record
selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd
line
to
check if the value of SORMA within the table SORMA_Results is valid,
i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0 Then...
and
so
on but keep getting a debug that says the Field | cannot be found.

How do I correctly address a Table & Field within an IF THEN
Statement?

Is there a Reference File anywhere which lists this kind of
information
as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green


:

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that it
effectively filters the main form to show only records that have a
match
in
the subform (by assigning an INNER JOIN statement to its
RecordSource),
and
how to combine that technique with filters on the main form's
fields
at
the
same time.

Hi All,
I have searched the groups for an answer but can't find it so
far...
I have a form 'Customers' and it has an embedded? Sub-Form
'Product'.
I have a Find button - via the cmd button wizard which brings up
the
Find
&
Replace Dialog. Having selected the field in either the Customer
or
Product
forms I can indeed 'Find' a record. However, whilst the Customer
Find
is
OK,
the Product Find will only let me find records relating to the
selected
customer. What I need is a Find that will allow me to search for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to find
the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to somewhere
I
can
learn how to do it. I am familiar with VBA it's just how you pull
this
info
together I can't work out. New to SQL etc.

 

[Allen Browne]

If you have a query that contains 2 tables with an INNER JOIN basd on a
one-to-many relation, I would expect that you could edit the fields from the
related table and add or delete records to that table.

If you want to go the other way, or if you have more tables, then you might
be able to use a subquery in the WHERE clause and still have an editable
query. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
Thanks for the answer. I guess I'm not as clued up as I thought but I'm
getting there.

I have found that the record for Make can be selected if the Table/Query
used for the source contains it. So I now have a search form that I can
search for an RS Number or SORMA Number or Make or Model. So far so good.

The problem is I have to use a query as a source which uses fields from
TWO
tables. however I can't edit the records. If the source for the form is a
table or a Query with only fields from one table then I can.

What I would like is to search one table for the Make - thats on the
subform
and has a SORMA Number in it and but brings the record up for the other
table
which also has the SORMA number in it.

So I would look up the Make and return the SORMA number then use this for
the Open Form --- Whereis command

So the dlookup searches for Make but returns SORMA number. Is this
possible
without a query linking two tables or am I doing something basically wrong
with the whole setup?

--
Cheers
Chas

***************
* Spectrum is Green *
***************

Sure. You can put 2 conditions into strWhere, e.g.:
If Not (IsNull(Me.txtFindMake) Or IsNull(Me.RS)) Then
strWhereMake = "([Make] = '" & Me.txtFindMake & _
"') AND ([RS] = " & Me.RS & ")"

The Criteria argument of DLookup() is just like the WHERE clause in a
query.
You can mock up a query, switch it to SQL View (View menu, from query
design), and Access will show you what your criteria string should look
like.

Hi Allen, Me again.
I'm getting used to the dlookup and it's working well for me.
My question is can I lookup TWO attributes with it?
I want to lookup the Product Make and the RS number so that when I have
the
Make it also gives me the RS Number which I can then use as the trigger
for
the form Whereis attribute.

The reason for this is the old Subform dilemma. I have the Make
displayed
in
the subform but want the find to bring up the Main forms' record.

Here's what I've done so far...

Private Sub txtFindMake_AfterUpdate()
'Find a Make in the RS table and Display from the RSNO
Dim strWhereMake As String
Dim strFoundMake As String
Dim FoundRSNo As Integer

If Not IsNull(Me.txtFindMake) Then
strWhereMake = "[Make] = '" & Me.txtFindMake & "'"
strFoundMake = DLookup("Make", "RS_Results", strWhereMake)
MsgBox strFoundMake, vbOKCancel, "Here is the Make" ' This is just
to
test the find

'Close this form
DoCmd.Close
'Make the RELEASE Button visible
Forms!sorma_records_edit.cmdReleaseFilter.Visible = True
'Open the Edit form at the appropriate record

DoCmd.OpenForm "Sorma_Records_Edit", , , strWhereMake

End If
End Sub

the DoCmd does not work as the Field Make is on the subform

What I want is the RS Number for that Make (there may be many
occurrences
of
this but I'll deal with that later) so that I can display the Main form
from
the RS Number.

Hope that's clear.

I know it's getting complicated and I really need to go back to your
http://allenbrowne.com/ser-28.html
page and work on this but I just thought that the dlookup for multiple
attributes might be easier for now.

--
Cheers
Chas

***************
* Spectrum is Green *
***************


:

DLookup() would be the simplest way to see if the number is in the
table.
It
will return Null if the entry is not found.

Use the BeforeUpdate event of you want to keep the user in the box
until
they enter something valid.

Private Sub txtSearchSorma_BeforeUpdate(Cancel As Integer)
Dim strWhere As String

If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If IsNull(DLookup("SORMA", "Table1", strWhere)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End With
End Sub

More info on DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the
table,
I
know there is, what I want to do is validate that the record with
the
number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g.,
2244)
I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is
shown
with
that value in the SORMA field box. If I enter a false value, a blank
record
is shown with the value 0 in the SORMA field Box.
--

:

I think you are trying to determine whether there are any records
in
the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered
as
I'm
still
learning and have ordered a couple of books on Access to learn
more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a
better
one?

I have tried one of your earlier tips on searching and made that
work.
I
have Created a form which opens on clicking a button on the main
form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text
boxes
which
searches for a record field then opens the data form at that
record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric
field.
This works well if the record exists but if not then the record
selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd
line
to
check if the value of SORMA within the table SORMA_Results is
valid,
i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0
Then...
and
so
on but keep getting a debug that says the Field | cannot be
found.

How do I correctly address a Table & Field within an IF THEN
Statement?

Is there a Reference File anywhere which lists this kind of
information
as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green


:

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that
it
effectively filters the main form to show only records that have
a
match
in
the subform (by assigning an INNER JOIN statement to its
RecordSource),
and
how to combine that technique with filters on the main form's
fields
at
the
same time.

message
Hi All,
I have searched the groups for an answer but can't find it so
far...
I have a form 'Customers' and it has an embedded? Sub-Form
'Product'.
I have a Find button - via the cmd button wizard which brings
up
the
Find
&
Replace Dialog. Having selected the field in either the
Customer
or
Product
forms I can indeed 'Find' a record. However, whilst the
Customer
Find
is
OK,
the Product Find will only let me find records relating to the
selected
customer. What I need is a Find that will allow me to search
for
records
in
the Product Table and bring up the Customer Record and the
corresponding
Product Record in the sub-form.
The Forms Record Sources are the relevent Tables not a query.
Do I need to build some form of SEARCH Dialog of my own to
find
the
relevent
records? is the standard Find button too simple?
Here is the code for the Find Button:-

Private Sub cmdFindCUSTRec_Click()
'Search for Customer Records
On Error GoTo Err_cmdFindCUSTRec_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindCUSTRec_Click:
Exit Sub

Err_cmdFindCUSTRec_Click:
MsgBox Err.Description
Resume Exit_cmdFindCUSTRec_Click

End Sub

I don't want someone to do this for me just point me to
somewhere
I
can
learn how to do it. I am familiar with VBA it's just how you
pull
this
info
together I can't work out. New to SQL etc.

 

[Guest]

Hi Allen,
Discovered the problem with the Query, did not have the Primary Key set for
the SORMA field in the first of the two tables. Setting this Field as the PK
now allows me to use the query and edit the records AND search for data.

Many thanks for all your advice.

PS, what do you know about Deleting folders? I placed a post on the
Windows/Filesystem board yesterday and no replies so far. If you could help
on this I'd be obliged.

Post is called "Corrupted Folder? Cannot Delete or take ownership.

(Sorry don't know how to derive a link from there to copy to here).
--
Cheers
Chas

***************
* Spectrum is Green *
***************

If you have a query that contains 2 tables with an INNER JOIN basd on a
one-to-many relation, I would expect that you could edit the fields from the
related table and add or delete records to that table.

If you want to go the other way, or if you have more tables, then you might
be able to use a subquery in the WHERE clause and still have an editable
query. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
Thanks for the answer. I guess I'm not as clued up as I thought but I'm
getting there.

I have found that the record for Make can be selected if the Table/Query
used for the source contains it. So I now have a search form that I can
search for an RS Number or SORMA Number or Make or Model. So far so good.

The problem is I have to use a query as a source which uses fields from
TWO
tables. however I can't edit the records. If the source for the form is a
table or a Query with only fields from one table then I can.

What I would like is to search one table for the Make - thats on the
subform
and has a SORMA Number in it and but brings the record up for the other
table
which also has the SORMA number in it.

So I would look up the Make and return the SORMA number then use this for
the Open Form --- Whereis command

So the dlookup searches for Make but returns SORMA number. Is this
possible
without a query linking two tables or am I doing something basically wrong
with the whole setup?

--
Cheers
Chas

***************
* Spectrum is Green *
***************

Sure. You can put 2 conditions into strWhere, e.g.:
If Not (IsNull(Me.txtFindMake) Or IsNull(Me.RS)) Then
strWhereMake = "([Make] = '" & Me.txtFindMake & _
"') AND ([RS] = " & Me.RS & ")"

The Criteria argument of DLookup() is just like the WHERE clause in a
query.
You can mock up a query, switch it to SQL View (View menu, from query
design), and Access will show you what your criteria string should look
like.

Hi Allen, Me again.
I'm getting used to the dlookup and it's working well for me.
My question is can I lookup TWO attributes with it?
I want to lookup the Product Make and the RS number so that when I have
the
Make it also gives me the RS Number which I can then use as the trigger
for
the form Whereis attribute.

The reason for this is the old Subform dilemma. I have the Make
displayed
in
the subform but want the find to bring up the Main forms' record.

Here's what I've done so far...

Private Sub txtFindMake_AfterUpdate()
'Find a Make in the RS table and Display from the RSNO
Dim strWhereMake As String
Dim strFoundMake As String
Dim FoundRSNo As Integer

If Not IsNull(Me.txtFindMake) Then
strWhereMake = "[Make] = '" & Me.txtFindMake & "'"
strFoundMake = DLookup("Make", "RS_Results", strWhereMake)
MsgBox strFoundMake, vbOKCancel, "Here is the Make" ' This is just
to
test the find

'Close this form
DoCmd.Close
'Make the RELEASE Button visible
Forms!sorma_records_edit.cmdReleaseFilter.Visible = True
'Open the Edit form at the appropriate record

DoCmd.OpenForm "Sorma_Records_Edit", , , strWhereMake

End If
End Sub

the DoCmd does not work as the Field Make is on the subform

What I want is the RS Number for that Make (there may be many
occurrences
of
this but I'll deal with that later) so that I can display the Main form
from
the RS Number.

Hope that's clear.

I know it's getting complicated and I really need to go back to your
http://allenbrowne.com/ser-28.html
page and work on this but I just thought that the dlookup for multiple
attributes might be easier for now.

--
Cheers
Chas

***************
* Spectrum is Green *
***************


:

DLookup() would be the simplest way to see if the number is in the
table.
It
will return Null if the entry is not found.

Use the BeforeUpdate event of you want to keep the user in the box
until
they enter something valid.

Private Sub txtSearchSorma_BeforeUpdate(Cancel As Integer)
Dim strWhere As String

If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
If IsNull(DLookup("SORMA", "Table1", strWhere)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End With
End Sub

More info on DLookup():
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
Thanks again for the speedy reply.
Actually I'm not looking to see if there are ANY records in the
table,
I
know there is, what I want to do is validate that the record with
the
number
entered by the user exists.

The table has several dummy entries at present.
The SORMA field is not the autonumber field it is a numeric field.
The data at present looks like 1231, 1232, 1234, 1235 etc.
If the user enters any number which is NOT in the field SORMA (e.g.,
2244)
I
want to tell him and put the focus back to the text box.

Currently when I enter a number thats in the Table the record is
shown
with
that value in the SORMA field box. If I enter a false value, a blank
record
is shown with the value 0 in the SORMA field Box.
--

:

I think you are trying to determine whether there are any records
in
the
form Sorma_Results when you open it?

If so:
If Forms!Sorma_Results.RecordsetClone.RecordCount > 0 Then
MsgBox "Yep: found some."
End If

Thanks for the link Allen,
I had looked at this before when trawling through the groups.
Unfortunately
I cannot seem to get it to work and for now am not too bothered
as
I'm
still
learning and have ordered a couple of books on Access to learn
more.
(Access
2000 Bible & MS Access Step by step). Could you recommend a
better
one?

I have tried one of your earlier tips on searching and made that
work.
I
have Created a form which opens on clicking a button on the main
form.
Here
the user can enter a number for one of either two searches, I'll
incorporate
more when I can.

Here is the code from the After Update event of one of the Text
boxes
which
searches for a record field then opens the data form at that
record.

Private Sub txtSearchSorma_AfterUpdate()
'Find a SORMA Number
Dim strWhere As String
If Not IsNull(Me.txtSearchSorma) Then
strWhere = "[SORMA] = " & Me.txtSearchSorma
DoCmd.OpenForm "Sorma_Results", , , strWhere
End If
End Sub

The Table is SORMA_Results and the Field is "SORMA" a numeric
field.
This works well if the record exists but if not then the record
selected
is
blank with the value for SORMA showing 0 (zero).

What I want to do is put in an IF THEN statement before the DoCmd
line
to
check if the value of SORMA within the table SORMA_Results is
valid,
i.e.,
it
has a value.

I have tried various forms of IF [SORMA_Results]![SORMA] = 0
Then...
and
so
on but keep getting a debug that says the Field | cannot be
found.

How do I correctly address a Table & Field within an IF THEN
Statement?

Is there a Reference File anywhere which lists this kind of
information
as
I
cannot seem to find it in Access help.

Cheers

Chas


--
Spectrum is Green


:

Hi Chas

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to respond to the user input to that