Newbie needs help

[larryofox]

My form gets its data from a query with two tables. One table, a voter file, includes a unique voter id number – the primary key.

I want users to find records by entering or scanning the voter id number into a control without using a command button or dialogue box.

I know Access supports this approach. I used it once before. But I cannot recall how I set it up.

Can you please help me? Thanks.

Larry

ps. I don't know VBA and with only two weeks remaining before the election I have no time to learn it now. Could you please provide a macro based solution. Thanks again.

 

[WayneRyan]

Larry,

Look in the Sample Database forum here for Searching databases. Also use
the Search Facility and look for "Search". You will find many examples.

Wayne

 

[larryofox]

Could I trouble you again?

Hi Wayne,

Thanks for your suggestions.

Looking in the Databases section for “search database” returned only four examples. None proved useful.

So I scoured the Forms section and found many examples but no solution.

Selecting records from a drop down list won’t work. My primary table contains over 600,000 records. And I do not understand the VBA code examples.

Can you point me to a solution that permits users to select a record by entering a unique record identification number in a control?

Thanks again for your help.

Larry

 

[WayneRyan]

Larry,

Granted, with over 600,000 records, a dropdown list won't work.

Have you done a search on "Cascading"? You can have a 2/3/4
level set of combo boxes to "fine tune" the search. Might be
a really good way to go here.

Alternatively, you can add an unbound control to your form, let's
call it txtSearch.

Set its DefaultValue to an existing ID Number.

Base your form on a query which has the following criteria for
the ID Number:

=Forms![YourMainForm]![txtSearch]

The form will open up to ONLY that record! Way better than
all 600,000!

Then use the AfterUpdate event of the txtSearch field to put
this line of code:

Me.ReQuery

Now, whenever someone types a new number in the txtSearch field,
the form will requery and display that record.

That's OK for short-term, but maybe you will get some additional
ideas after looking at Cascading combos. Your real solution
should reside there.

let me know,
Wayne

 

[larryofox]

i'm trying but...

hi wayne,

added an unbound text box named txtSearch with default value equal to a valid id number.

changed criteria of the id number of the query to
Forms![VoterFileMaintenanceFormCOPY]![txtSearch]

now the query returns no records.

added to the after update event of the txtSearch control

Private Sub txtSearch_AfterUpdate()
Me.Requery
End Sub


now the form selects no records and typing in a valid id number accomplishes nothing. tried building a macro too. same results.

please let me know where i went wrong. thanks again for your help.

larry

 

[jon98548]

Larry, look at the second post here by jfgambit. I think it might suit your needs. I'm also looking for another one I used once.Search form

 

[larryofox]

thanks but...

thanks jon.

i appreciate your time and trouble but i do not understand VBA code.

i don't need complex layers of filtering.

i want to scan in a unique primary key value to select a record.

seems pretty simple. i did it once before but i do not remember how.

if you think of another method i would certainly appreciate hearing from you.

thanks,

larry

 

[WayneRyan]

Larry,

Sounds like you are close.

What happens if you remove the criteria from the query? Do you get
all records?

Do you have any masks on your table?

Can you post a database here with only a couple of records? If so,
make sure to Tools --> Compact/Repair first, Then ZIP.

Wayne

 

[larryofox]

attached

Hello Wayne,

Thanks for getting back to me.

What happens if you remove the criteria from the query? Do you get
all records?

Yes.

Do you have any masks on your table?

No.

Can you post a database here with only a couple of records? If so,
make sure to Tools --> Compact/Repair first, Then ZIP.

Yes. I believe I removed the criteria from the query and the text box from the form before compacting the database.

I did not create a primary key in the sample table I included.

Thanks again for your help.

Larry

 

[WayneRyan]

Larry,

I see that you're online. I just looked at it and didn't see "txtSearch".

Give me a couple of minutes.

Wayne

 

[larryofox]

txtSearch

hello again,

i removed the text box. the form is quite incomplete. no reason to finish it if i cannot select records the way i need to.

thanks,

larry

 

[WayneRyan]

Larry,

This is closer.

Your query didn't have all of the columns, there was no txtSearch and the
criteria was precinct.

Look at this, but we'll build on it from here.

We really want to use Cascading combos for this.

I'm thinking;

Precinct - How many precincts?
City
LastName

Wayne

 

[larryofox]

may now need a cascading combo box

hi wayne,

I may need to use a cascading combo box. I read my two Access manuals and examined this web site but didn't find anything I could understand. I even downloaded an example you provided another user. No luck.

But, I could probably meet my need with a command button on my form which runs a query that populates the fields on the form rather than simply displaying the results of the query.

Any way to do this? Thanks very much for your help.

Larry

 

[WayneRyan]

Larry,

If this is run assuming that a voter steps up, provides a Voter ID number,
and views their data fine.

I would imagine that they might forget their number.

What precinct?
What city?
Last Name?

Combo-boxes can be used to progressively narrow the search down.
They can be pretty nice.

As to the record being added, it's probably because the query joins two
tables. I'll have a look later. I didn't look at that first time.

Wayne

 

[larryofox]

hi wayne,

thanks for the help with the search issue.

we always know the voter id number so a progressive query isn't needed. we just scan in the value from a form.

but, could i please ask another question? as soon as we scan in the voter id number i would like the value of the Pres Pref and Assmbly Pref fields for that record in the underlying table to change to 1.

i looked at the SetValue action but could not figure out how to implement it.

thanks,

larry

 

[WayneRyan]

Larry,

I'm not following this, but you can use the OnCurrent event of the form to:

Me.SomeField = 1
Me.SomeOtherField = 1

You're not trying to sway the voter's choices are you?

Wayne

 

[larryofox]

no luck

hi wayne,

thanks for getting back to me. i tried your solution...

Me.Pres Pref=1
Me.Assmbly Pref=1

and got a compile error message - "Method or data member not found"

where did i go wrong this time?

no, we do not try to sway voters. we simply identify supporters and try to turn them out on e-day.

thanks again for your time. i really appreciate it.

larry

 

[WayneRyan]

Larry,

When they scan in the Voter ID #, you will use the form's OnCurrent event.

I don't know why you want to set them to 1, but:

Me.[Pres Pref] = 1
Me.[Assmbly Pref] = 1

is all you need.

Wayne

 

[larryofox]

i attached the database

Hello Wayne,

I added the brackets to

Me.[Pres Pref] = 1
Me.[Assmbly Pref] = 1

That resulted in a run time error 2465 "Microsoft Access can't fine the field "|" referred to in your expression."

I noticed that spaces in field names can make VBA unhappy so I removed the spaces and made sure the query and form also contained the new field name. No help. Same error message.

So I attached the database. Could you take a look and tell me why I keep shooting myself in the foot?

Then, I have another question about the txtSearch control.

After I finish editing the current record, how do I clear that record from my form and go to the txtSearch control to scan in a new id number?


I don't know why you want to set them to 1, but:

When users scan in a record, I want those fields updated to "1" in the table.

I hope you can help me. Thanks again for your time.

Larry

 

[WayneRyan]

Larry,

Those are option groups. Use the OnCurrent event of the form and put:

Me.Frame72 = 1
Me.Frame84 = 1

Wayne