Help the Newbie!

[Accessless]

Great forum!! Hope I someone on here can help me!

Here's the situation. I have a Database in Excel (~3000 rows & 7 Columns)

The first Column has numbers like this (3-10-34 or 223-7-45) the other columns have names, addresses and other stuff.

What I'm trying to do is to eliminate the use of huge binders. I want to have a Blank screen with only one input box where I can type a number and retrieve a specific row from my Database. Time saving.

Now, when I'm searching, I usually only have the first numbers before the dash (such as the 3 or the 223) not the other numbers. So I cannot input 3-10-34 for instance. I can only put 3...

I've already imported it to Access and it works fine but have no idea how to do what I just described!

Thanks for the input!!

A very very green access user!

 

[andrew93]

If you are relatively new to Access. let's start with something simple like searching for the records you want. We can worry about displaying them on forms later.

Create a new query and base it on (i.e. add) the table that holds your imported data. In your query design screen, add the fields that you want to see in your final search results into the query grid (it's as simple as clicking and dragging). For simplicity purposes let's call your frst column 'code'. In your query design screen, under the code field, type in the following criteria :

Like [Enter Code Prefix] & "-*"

Save the query. When you open the query, you will be asked [Enter Code Prefix] where you should enter 3 or 223 per your example. I'm not sure if the prefix for the code is unique or not but this query will display all records that have the same prefix (i.e. the digits before the first -).

BTW you can change the description 'Enter Code Prefix' to whatever you like.

HTH, Andrew

 

[Accessless]

Andrew, first of all, thanks for tutoring me!

I tried exactly what you said and it worked (of course) I tested many numbers and even if there are numbers such as 3, 33, 333, 1033: it will only pull what I input--> Which is perfect!!

So this is my Query.

When I put a number in, it comes out as this

I created a form and linked it to the query. When I click on the Form, it opens up the initial query (Enter number) and then this form opens up, filled with the proper info!

Now all I really need is to have that Query button accesible in the same screen as the form so that at anytime, I can click it and get new info in that same form. Would the form reset automatically if I inputed a new number (which is what i want)?

Is there a way to lock everything so it can't be modified (like the query results??) What I'm trying to avoid is this. A new window pops up with results, someone can highlight a name and delete it. So I need to idiot proof (for myself and the others) this query and database thing.

Thanks again, I really appreciate it!

 

[andrew93]

You're welcome.

To stop the users editing or deleting records, go into the form design, click Edit -> Select Form, Click View -> Properties -> Data tab -> set AllowEdits, AllowDeletions and AllowDeletions to 'No'.

I'm not an expert with VB but a quick and dirty way of refreshing the screen is to create a button on the foot of your report (by clicking the appropriate button on the toolbox and clicking in your form footer, if you can;t see the Toolbox click View -> Toolbox) and click 'Cancel' when the command button wizard appears. While in the form design, click on the button, click Edit -> View Properties -> Event Tab -> OnClick -> click the three dots beside the arrow -> Code Builder -> ok. Enter the following code between the 'Private Sub ____ Click()' part and the 'End Sub' part :

On Error GoTo Err_Click

DoCmd.Close acForm, "frmMyFormName", acSaveNo
DoCmd.OpenForm "frmMyFormName"

Err_Click:
    Exit Sub

Save and close the VB editor window and save the form. Be sure to use your actual form name. Save the form and give it a test run.

HTH, Andrew

 

[Accessless]

Nice, thanks for your help! I did what you said and it seems to be working great! (let's burn the binders!! )

2 questions:

- When i enter a Client Number that doesn't exist, it takes me to a blank screen, how would I create a pop up message that says " Not Found, Please Enter Client Number" --> Something that would constantly loop as long as you don't enter a correct number?

- Can you compile this little tool so it can be used outside of Access?

Thanks again for your help!

 

[ScottGem]

May I suggest a different way that will do what you want. Bind your form directly to the table instead of using a query. In form design mode, use the Combobox wizard to create a search combo (the third option). Set the bound controls on the form to Locked=Yes so they can't be edited.

The user can then open the form start typing in what they know of the client number and the combo will attempt to complete the number. If there is no match they will immediately see that. You can set the Combo to tallow them to type in the client name instead or have 2 combos, one for name the other for number.

 

[Accessless]

Thanks,

I tries both methods and they both work fine. But, I still haven't figured out the Client Number "Not found" thing if I use the Query.

Is there a way to compress this into a little file so you don't have to open Access every time?

Thanks

 

[ScottGem]

Thanks,

I tries both methods and they both work fine. But, I still haven't figured out the Client Number "Not found" thing if I use the Query.

Is there a way to compress this into a little file so you don't have to open Access every time?

Thanks

But they don't both work fine if you have an issue with entering invalid Client Numbers. As a general design rule, you want to limit the amount of typing users do. Wherever possible given then a selection to pick from instead.
If you want to use the query you would after use an IF and a Dcount to make sure the returned records is not 0.

There is no way to make Access into a standalone app. You can load an Access app directly without having to first open Access then use File Open. Just create a shortcut to your data file.

 

[Accessless]

Well your method works really well but has one drawback (maybe it can be fixed) The arrow displayed in the combo box allows you to see the rest of the records. So if someone types in 38, it will complete it and find a record but if the user opens up the arrow, he might select the wrong client or even read the wrong info.

The other method, it pops up into a window and there are no other records to be viewed.

Is there a way around that? Thanks

 

[ScottGem]

Well your method works really well but has one drawback (maybe it can be fixed) The arrow displayed in the combo box allows you to see the rest of the records. So if someone types in 38, it will complete it and find a record but if the user opens up the arrow, he might select the wrong client or even read the wrong info.

The other method, it pops up into a window and there are no other records to be viewed.

Is there a way around that? Thanks

I don't see that as a drawback. Again, as a general rule its better to have users select from a list. Do your users really know client numbers? Wouldn't it be better to have them select a Client name? they can start typing until they get a match or browse the list to select a client. Once they do, they can review the info to make sure they have selected the right client.

 

[Accessless]

I see what you're saying! The way we operate is like this: A company calls us on behalf of a client, leaves a message using only a client number. We record this number and give it to a driver who is dispatched. Using his onboard laptop, he finds the appropriate info about the client (while driving) and attends to the situation. This is why I was leaning towards the first method because all he has to do is type the number, press Enter and prints the result. Minimizes the time his eyes are off the road.

For privacy reasons, the office can't start opening up files and dispatching all that info on radio so we just give a Client Number and the drivers can figure out the rest. Right now it's done using binders but often, you have a driver parked for 10 minutes trying to find the Info.

 

[ScottGem]

Ahh, workflow does impact on design. Have you looked into Voice Recognition? You might be able to do some neat stuff with allowing the driver to just speak the number.

Anyway, you can still use the NotInList event of the combobox to trap client numbers that don't exist if the driver wants to just type in the whole number.