Hi.

I'm very new to MS Access and have been presented with an Access database
of contacts by my employer.

I am trying to redesign the main form of the database so that a button
entitled 'search' may be clicked on by the user and the user can then
search all records by postcode. I want to do this to prevent duplicate
data entry.

I have searched the helpfiles and various forums but can't make head nor
tail of the basics. All the articles I look at are far too advanced and
I'm sure I'm just not getting the terminology right. Do I need to
'record a macro' or set up an event? I have the button on the form, but
nothing behind the button. I have all the data in tables behind the
form, but no 'query' (?) with which the user can search.

Any help gratefully received.

Thank you,

Steph.

Steph wrote:[color=blue]
> Hi.
>
> I'm very new to MS Access and have been presented with an Access database
> of contacts by my employer.
>
> I am trying to redesign the main form of the database so that a button
> entitled 'search' may be clicked on by the user and the user can then
> search all records by postcode. I want to do this to prevent duplicate
> data entry.
>
> I have searched the helpfiles and various forums but can't make head nor
> tail of the basics. All the articles I look at are far too advanced and
> I'm sure I'm just not getting the terminology right. Do I need to
> 'record a macro' or set up an event? I have the button on the form, but
> nothing behind the button. I have all the data in tables behind the
> form, but no 'query' (?) with which the user can search.
>
> Any help gratefully received.
>
> Thank you,
>
> Steph.[/color]

What do you mean by searching? Is the main form a single record form
and you want to present a form a list of records to search? Or is your
main form a continuous/datasheet form and you want to filter the
records? Do you want the main form to filter to records with that post
(postal code?) or to a specific record?

You could do something as simple as using an inputbox to get the post
code. Ex:
Private Sub GetPostCode
Dim strPostCode As String
strPostCode = InputBox("What post code are you searching for?",
"Post Code Search")
If strPostCode > "" Then
Me.filter = "PostCode = "" & strPostCode & ""
Me.FilterOn = True
End If

If you could provide a brief description with some more specifics, you
may get a better response.

Salad <oil@vinegar.com> wrote in
news:LEzbe.1637$Oz2.262@newsread3.news.pas.earthli nk.net:
[color=blue]
> Steph wrote:[color=green]
>> Hi.
>>
>> I'm very new to MS Access and have been presented with an Access
>> database of contacts by my employer.
>>
>> I am trying to redesign the main form of the database so that a
>> button entitled 'search' may be clicked on by the user and the user
>> can then search all records by postcode. I want to do this to
>> prevent duplicate data entry.
>>
>> I have searched the helpfiles and various forums but can't make head
>> nor tail of the basics. All the articles I look at are far too
>> advanced and I'm sure I'm just not getting the terminology right. Do
>> I need to 'record a macro' or set up an event? I have the button on
>> the form, but nothing behind the button. I have all the data in
>> tables behind the form, but no 'query' (?) with which the user can
>> search.
>>
>> Any help gratefully received.
>>
>> Thank you,
>>
>> Steph.[/color]
>
> What do you mean by searching? Is the main form a single record form
> and you want to present a form a list of records to search? Or is
> your main form a continuous/datasheet form and you want to filter the
> records? Do you want the main form to filter to records with that
> post (postal code?) or to a specific record?[/color]

Er... I am guessing it's the latter. There are 900 business records in
this database. Company name, address phone number etc. The front end
screen (form?) is just a series of user buttons and, for the user, I
don't want it to get more complicated than that. I know that I can look
up a postcode if I click into the postcode field on the database, and
then click on the binoculars - but I need a user to be able to do that
without a) accessing the raw database and working only from the form and
b) taxing their brain too heavily.
[color=blue]
> You could do something as simple as using an inputbox to get the post
> code. Ex:
> Private Sub GetPostCode
> Dim strPostCode As String
> strPostCode = InputBox("What post code are you searching for?",
> "Post Code Search")
> If strPostCode > "" Then
> Me.filter = "PostCode = "" & strPostCode & ""
> Me.FilterOn = True
> End If
>
> If you could provide a brief description with some more specifics, you
> may get a better response.[/color]

I don't mean to be rude, but the above may as well be in Japanese for all
the sense I can make of it.

I have in front of me a "form" - an MS Access input screen which has a
database behind it - this form contains buttons I can click on. All of
which currently perform an action, except the 'search' button. This is
the form already created by my employer. What I am trying to do is
improve upon it and offer a way users can search under postcode (and
perhaps other criteria too) so that when they are entering the details of
a potentially new company, into the database, they can rule out the
possibility of a duplicate entry beforehand.

I don't need it to generate a report per se, just say "no records with
that, or part of that, postcode were found"





--
http://www.tosaveme.blogspot.com

Puck wrote:
[color=blue]
> Salad <oil@vinegar.com> wrote in
> news:LEzbe.1637$Oz2.262@newsread3.news.pas.earthli nk.net:
>
>[color=green]
>>Steph wrote:
>>[color=darkred]
>>>Hi.
>>>
>>>I'm very new to MS Access and have been presented with an Access
>>>database of contacts by my employer.
>>>
>>>I am trying to redesign the main form of the database so that a
>>>button entitled 'search' may be clicked on by the user and the user
>>>can then search all records by postcode. I want to do this to
>>>prevent duplicate data entry.
>>>
>>>I have searched the helpfiles and various forums but can't make head
>>>nor tail of the basics. All the articles I look at are far too
>>>advanced and I'm sure I'm just not getting the terminology right. Do
>>>I need to 'record a macro' or set up an event? I have the button on
>>>the form, but nothing behind the button. I have all the data in
>>>tables behind the form, but no 'query' (?) with which the user can
>>>search.
>>>
>>>Any help gratefully received.
>>>
>>>Thank you,
>>>
>>>Steph.[/color]
>>
>>What do you mean by searching? Is the main form a single record form
>>and you want to present a form a list of records to search? Or is
>>your main form a continuous/datasheet form and you want to filter the
>>records? Do you want the main form to filter to records with that
>>post (postal code?) or to a specific record?[/color]
>
>
> Er... I am guessing it's the latter. There are 900 business records in
> this database. Company name, address phone number etc. The front end
> screen (form?) is just a series of user buttons and, for the user, I
> don't want it to get more complicated than that. I know that I can look
> up a postcode if I click into the postcode field on the database, and
> then click on the binoculars - but I need a user to be able to do that
> without a) accessing the raw database and working only from the form and
> b) taxing their brain too heavily.
>
>[color=green]
>>You could do something as simple as using an inputbox to get the post
>>code. Ex:
>>Private Sub GetPostCode
>> Dim strPostCode As String
>> strPostCode = InputBox("What post code are you searching for?",
>>"Post Code Search")
>> If strPostCode > "" Then
>> Me.filter = "PostCode = "" & strPostCode & ""
>> Me.FilterOn = True
>> End If
>>
>>If you could provide a brief description with some more specifics, you
>>may get a better response.[/color]
>
>
> I don't mean to be rude, but the above may as well be in Japanese for all
> the sense I can make of it.
>
> I have in front of me a "form" - an MS Access input screen which has a
> database behind it - this form contains buttons I can click on. All of
> which currently perform an action, except the 'search' button. This is
> the form already created by my employer. What I am trying to do is
> improve upon it and offer a way users can search under postcode (and
> perhaps other criteria too) so that when they are entering the details of
> a potentially new company, into the database, they can rule out the
> possibility of a duplicate entry beforehand.
>
> I don't need it to generate a report per se, just say "no records with
> that, or part of that, postcode were found"[/color]

There are many ways to search for a something. I have created a generic
form for my app. It contains a form and subform. I pass to the form
which table I want to use. I then create a SQL statement, on the fly,
to display the fields for whichever table I am displaying. I then have
a combo box where the op selects the field to search on and a text field
to enter the value to search. It then displays all records matching
that criteria in the search field.

In another method, I present a continuos form. Above each label for
each column I present a textbox. When a person enters something in the
textbox, I then search for the first occurrance in that column that
matches the value entered by the op...I allow the first part of the
column or if the value is contained in any part of the field. Using the
F3 key, I can find the next occurrence.

It is very easy for me to update these search methods since I wrote
them, understand them, and can program. If you are new to this, the
search part may be a bit difficult, especially if you are new to
programming.

The first step you need to come up with is a method you think would be
easy for the user to use to find a record. Maybe a combo box is all you
need. For example, you could have a combo box that presents a list of
search fields. The fields might be Name, PostCode, City, State, etc.
The second combo lists all the records in the cust table. The rowsource
may be

Select ContactID, CustName, CustCity, CustPostCode From Customers Order
By CustName.

You could hid the ContactID by setting its with to 0.


In the AfterUpdate event of the first combo, change the SQL for the
second combo. Ex:
DIm strSQL As String
If Me.combo1 = "Name" Then
strSQL = " Select ContactID, CustName, CustCity, CustState,
CustPostCode From Customers Order By CustName"
elseIf Me.combo1 = "PostCode" Then
strSQL = " Select ContactID, CustPostCode, CustName, CustCity,
CustState From Customers Order By CustPostCode"
elseIf Me.combo1 = "City" Then
strSQL = " Select ContactID, CustCity, CustName, CustState,
CustPostCode From Customers Order By CustCity
ENdif
Me.Combo2.RowSOurce = strSQL

Now the person can start entering the value to search for and the second
combo will start getting closer and closer to the field being searched for.

You can then do what you want based on the search results.

It all depends on how deep you want to get into this. Good luck.

Salad <oil@vinegar.com> wrote in
news:u_Fbe.14809$An2.2213@newsread2.news.pas.earth link.net:
[color=blue]
> Puck wrote:
>[color=green]
>> Salad <oil@vinegar.com> wrote in
>> news:LEzbe.1637$Oz2.262@newsread3.news.pas.earthli nk.net:
>>
>>[color=darkred]
>>>Steph wrote:
>>>
>>>>Hi.
>>>>
>>>>I'm very new to MS Access and have been presented with an Access
>>>>database of contacts by my employer.
>>>>
>>>>I am trying to redesign the main form of the database so that a
>>>>button entitled 'search' may be clicked on by the user and the user
>>>>can then search all records by postcode. I want to do this to
>>>>prevent duplicate data entry.
>>>>
>>>>I have searched the helpfiles and various forums but can't make head
>>>>nor tail of the basics. All the articles I look at are far too
>>>>advanced and I'm sure I'm just not getting the terminology right.
>>>>Do I need to 'record a macro' or set up an event? I have the button
>>>>on the form, but nothing behind the button. I have all the data in
>>>>tables behind the form, but no 'query' (?) with which the user can
>>>>search.
>>>>
>>>>Any help gratefully received.
>>>>
>>>>Thank you,
>>>>
>>>>Steph.
>>>
>>>What do you mean by searching? Is the main form a single record form
>>>and you want to present a form a list of records to search? Or is
>>>your main form a continuous/datasheet form and you want to filter the
>>>records? Do you want the main form to filter to records with that
>>>post (postal code?) or to a specific record?[/color]
>>
>>
>> Er... I am guessing it's the latter. There are 900 business records
>> in this database. Company name, address phone number etc. The front
>> end screen (form?) is just a series of user buttons and, for the
>> user, I don't want it to get more complicated than that. I know that
>> I can look up a postcode if I click into the postcode field on the
>> database, and then click on the binoculars - but I need a user to be
>> able to do that without a) accessing the raw database and working
>> only from the form and b) taxing their brain too heavily.
>>
>>[color=darkred]
>>>You could do something as simple as using an inputbox to get the post
>>>code. Ex:
>>>Private Sub GetPostCode
>>> Dim strPostCode As String
>>> strPostCode = InputBox("What post code are you searching for?",
>>>"Post Code Search")
>>> If strPostCode > "" Then
>>> Me.filter = "PostCode = "" & strPostCode & ""
>>> Me.FilterOn = True
>>> End If
>>>
>>>If you could provide a brief description with some more specifics,
>>>you may get a better response.[/color]
>>
>>
>> I don't mean to be rude, but the above may as well be in Japanese for
>> all the sense I can make of it.
>>
>> I have in front of me a "form" - an MS Access input screen which has
>> a database behind it - this form contains buttons I can click on.
>> All of which currently perform an action, except the 'search' button.
>> This is the form already created by my employer. What I am trying
>> to do is improve upon it and offer a way users can search under
>> postcode (and perhaps other criteria too) so that when they are
>> entering the details of a potentially new company, into the database,
>> they can rule out the possibility of a duplicate entry beforehand.
>>
>> I don't need it to generate a report per se, just say "no records
>> with that, or part of that, postcode were found"[/color]
>
> There are many ways to search for a something. I have created a
> generic form for my app. It contains a form and subform. I pass to
> the form which table I want to use. I then create a SQL statement, on
> the fly, to display the fields for whichever table I am displaying. I
> then have a combo box where the op selects the field to search on and
> a text field to enter the value to search. It then displays all
> records matching that criteria in the search field.
>
> In another method, I present a continuos form. Above each label for
> each column I present a textbox. When a person enters something in
> the textbox, I then search for the first occurrance in that column
> that matches the value entered by the op...I allow the first part of
> the column or if the value is contained in any part of the field.
> Using the F3 key, I can find the next occurrence.
>
> It is very easy for me to update these search methods since I wrote
> them, understand them, and can program. If you are new to this, the
> search part may be a bit difficult, especially if you are new to
> programming.
>
> The first step you need to come up with is a method you think would be
> easy for the user to use to find a record. Maybe a combo box is all
> you need. For example, you could have a combo box that presents a
> list of search fields. The fields might be Name, PostCode, City,
> State, etc. The second combo lists all the records in the cust table.
> The rowsource may be
>
> Select ContactID, CustName, CustCity, CustPostCode From Customers
> Order
> By CustName.
>
> You could hid the ContactID by setting its with to 0.
>
>
> In the AfterUpdate event of the first combo, change the SQL for the
> second combo. Ex:
> DIm strSQL As String
> If Me.combo1 = "Name" Then
> strSQL = " Select ContactID, CustName, CustCity,
> CustState,
> CustPostCode From Customers Order By CustName"
> elseIf Me.combo1 = "PostCode" Then
> strSQL = " Select ContactID, CustPostCode, CustName,
> CustCity,
> CustState From Customers Order By CustPostCode"
> elseIf Me.combo1 = "City" Then
> strSQL = " Select ContactID, CustCity, CustName,
> CustState,
> CustPostCode From Customers Order By CustCity
> ENdif
> Me.Combo2.RowSOurce = strSQL
>
> Now the person can start entering the value to search for and the
> second combo will start getting closer and closer to the field being
> searched for.
>
> You can then do what you want based on the search results.
>
> It all depends on how deep you want to get into this. Good luck.[/color]

Thanks for trying, Salad. It's still going right over my head and
bouncing off the walls behind me.

I just wondered if there was a standard event procedure, or macro (I
still don't understand the difference, despite flogging the sodding
helpfile all night) for data look-up. Since it's an integral part of
working with any data input interface, I expected it to be a standard
procedure. Clearly it isn't and I'm not a programmer. They don't pay me
enough to be arsed with all that.

No harm in trying though.

Thanks again.

Steph wrote:
[color=blue]
> Salad <oil@vinegar.com> wrote in
> news:u_Fbe.14809$An2.2213@newsread2.news.pas.earth link.net:
>
>[color=green]
>>Puck wrote:
>>
>>[color=darkred]
>>>Salad <oil@vinegar.com> wrote in
>>>news:LEzbe.1637$Oz2.262@newsread3.news.pas.eart hlink.net:
>>>
>>>
>>>
>>>>Steph wrote:
>>>>
>>>>
>>>>>Hi.
>>>>>
>>>>>I'm very new to MS Access and have been presented with an Access
>>>>>database of contacts by my employer.
>>>>>
>>>>>I am trying to redesign the main form of the database so that a
>>>>>button entitled 'search' may be clicked on by the user and the user
>>>>>can then search all records by postcode. I want to do this to
>>>>>prevent duplicate data entry.
>>>>>
>>>>>I have searched the helpfiles and various forums but can't make head
>>>>>nor tail of the basics. All the articles I look at are far too
>>>>>advanced and I'm sure I'm just not getting the terminology right.
>>>>>Do I need to 'record a macro' or set up an event? I have the button
>>>>>on the form, but nothing behind the button. I have all the data in
>>>>>tables behind the form, but no 'query' (?) with which the user can
>>>>>search.
>>>>>
>>>>>Any help gratefully received.
>>>>>
>>>>>Thank you,
>>>>>
>>>>>Steph.
>>>>
>>>>What do you mean by searching? Is the main form a single record form
>>>>and you want to present a form a list of records to search? Or is
>>>>your main form a continuous/datasheet form and you want to filter the
>>>>records? Do you want the main form to filter to records with that
>>>>post (postal code?) or to a specific record?
>>>
>>>
>>>Er... I am guessing it's the latter. There are 900 business records
>>>in this database. Company name, address phone number etc. The front
>>>end screen (form?) is just a series of user buttons and, for the
>>>user, I don't want it to get more complicated than that. I know that
>>>I can look up a postcode if I click into the postcode field on the
>>>database, and then click on the binoculars - but I need a user to be
>>>able to do that without a) accessing the raw database and working
>>>only from the form and b) taxing their brain too heavily.
>>>
>>>
>>>
>>>>You could do something as simple as using an inputbox to get the post
>>>>code. Ex:
>>>>Private Sub GetPostCode
>>>> Dim strPostCode As String
>>>> strPostCode = InputBox("What post code are you searching for?",
>>>>"Post Code Search")
>>>> If strPostCode > "" Then
>>>> Me.filter = "PostCode = "" & strPostCode & ""
>>>> Me.FilterOn = True
>>>> End If
>>>>
>>>>If you could provide a brief description with some more specifics,
>>>>you may get a better response.
>>>
>>>
>>>I don't mean to be rude, but the above may as well be in Japanese for
>>>all the sense I can make of it.
>>>
>>>I have in front of me a "form" - an MS Access input screen which has
>>>a database behind it - this form contains buttons I can click on.
>>>All of which currently perform an action, except the 'search' button.
>>> This is the form already created by my employer. What I am trying
>>>to do is improve upon it and offer a way users can search under
>>>postcode (and perhaps other criteria too) so that when they are
>>>entering the details of a potentially new company, into the database,
>>>they can rule out the possibility of a duplicate entry beforehand.
>>>
>>>I don't need it to generate a report per se, just say "no records
>>>with that, or part of that, postcode were found"[/color]
>>
>>There are many ways to search for a something. I have created a
>>generic form for my app. It contains a form and subform. I pass to
>>the form which table I want to use. I then create a SQL statement, on
>>the fly, to display the fields for whichever table I am displaying. I
>>then have a combo box where the op selects the field to search on and
>>a text field to enter the value to search. It then displays all
>>records matching that criteria in the search field.
>>
>>In another method, I present a continuos form. Above each label for
>>each column I present a textbox. When a person enters something in
>>the textbox, I then search for the first occurrance in that column
>>that matches the value entered by the op...I allow the first part of
>>the column or if the value is contained in any part of the field.
>>Using the F3 key, I can find the next occurrence.
>>
>>It is very easy for me to update these search methods since I wrote
>>them, understand them, and can program. If you are new to this, the
>>search part may be a bit difficult, especially if you are new to
>>programming.
>>
>>The first step you need to come up with is a method you think would be
>>easy for the user to use to find a record. Maybe a combo box is all
>>you need. For example, you could have a combo box that presents a
>>list of search fields. The fields might be Name, PostCode, City,
>>State, etc. The second combo lists all the records in the cust table.
>>The rowsource may be
>>
>> Select ContactID, CustName, CustCity, CustPostCode From Customers
>> Order
>>By CustName.
>>
>>You could hid the ContactID by setting its with to 0.
>>
>>
>>In the AfterUpdate event of the first combo, change the SQL for the
>>second combo. Ex:
>> DIm strSQL As String
>> If Me.combo1 = "Name" Then
>> strSQL = " Select ContactID, CustName, CustCity,
>> CustState,
>>CustPostCode From Customers Order By CustName"
>> elseIf Me.combo1 = "PostCode" Then
>> strSQL = " Select ContactID, CustPostCode, CustName,
>> CustCity,
>>CustState From Customers Order By CustPostCode"
>> elseIf Me.combo1 = "City" Then
>> strSQL = " Select ContactID, CustCity, CustName,
>> CustState,
>>CustPostCode From Customers Order By CustCity
>> ENdif
>> Me.Combo2.RowSOurce = strSQL
>>
>>Now the person can start entering the value to search for and the
>>second combo will start getting closer and closer to the field being
>>searched for.
>>
>>You can then do what you want based on the search results.
>>
>>It all depends on how deep you want to get into this. Good luck.[/color]
>
>
> Thanks for trying, Salad. It's still going right over my head and
> bouncing off the walls behind me.
>
> I just wondered if there was a standard event procedure,[/color]

No. The Combobox, if the form is bound, will present 3 options...the
third being to find a record based on value entered.

[color=blue]
> or macro (I still don't understand the difference, despite flogging the sodding
> helpfile all night) for data look-up. Since it's an integral part of
> working with any data input interface, I expected it to be a standard
> procedure.[/color]

No. Unfortunately, there are many ways to search for a record.

Clearly it isn't and I'm not a programmer. They don't pay me[color=blue]
> enough to be arsed with all that.[/color]

I know what you mean. I'm not sure if there's something in NorthWind
that will help you in finding a record but you may want to look at it.
Open up Northwind and check it out. Also, when you open up Access, you
can create various databases (click on Database Wizard...in fact there
is a contact manager as one of the databases). Maybe one of them has a
form with search capabilities.
[color=blue]
> No harm in trying though.
>
> Thanks again.[/color]

Good luck.

"Steph" <steph24@hotmail.com> wrote in message
news:Xns9644E4C2782AAHELP@130.88.202.229...[color=blue]
> Hi.
>
> I'm very new to MS Access and have been presented with an Access database
> of contacts by my employer.
>
> I am trying to redesign the main form of the database so that a button
> entitled 'search' may be clicked on by the user and the user can then
> search all records by postcode. I want to do this to prevent duplicate
> data entry.
>
>[/color]
This ought to be fairly straightforward. Is the post code stored in its own
field or is it part of another?

Regards,
Keith.
www.keithwilby.com

"Keith" <keith.wilby@baeAWAYWITHITsystems.com> wrote in
news:426f5ab7$1_1@glkas0286.greenlnk.net:
[color=blue]
> "Steph" <steph24@hotmail.com> wrote in message
> news:Xns9644E4C2782AAHELP@130.88.202.229...[color=green]
>> Hi.
>>
>> I'm very new to MS Access and have been presented with an Access
>> database of contacts by my employer.
>>
>> I am trying to redesign the main form of the database so that a
>> button entitled 'search' may be clicked on by the user and the user
>> can then search all records by postcode. I want to do this to
>> prevent duplicate data entry.
>>
>>[/color]
> This ought to be fairly straightforward. Is the post code stored in
> its own field or is it part of another?[/color]

It's in the post code field.

"Steph" <steph24@hotmail.com> wrote in message
news:Xns96457C3CFFB0EHELP@130.88.202.229...[color=blue]
> "Keith" <keith.wilby@baeAWAYWITHITsystems.com> wrote in
> news:426f5ab7$1_1@glkas0286.greenlnk.net:
>[color=green]
>> "Steph" <steph24@hotmail.com> wrote in message
>> news:Xns9644E4C2782AAHELP@130.88.202.229...[color=darkred]
>>> Hi.
>>>
>>> I'm very new to MS Access and have been presented with an Access
>>> database of contacts by my employer.
>>>
>>> I am trying to redesign the main form of the database so that a
>>> button entitled 'search' may be clicked on by the user and the user
>>> can then search all records by postcode. I want to do this to
>>> prevent duplicate data entry.
>>>
>>>[/color]
>> This ought to be fairly straightforward. Is the post code stored in
>> its own field or is it part of another?[/color]
>
> It's in the post code field.[/color]

So you want to return all records containing a certain string in a text box?
Have you considered using the built-in "Filter by Form" feature?