Next item in list when item entered not found 2

[Malagar]

I work in shipping/receiving for a food shipment company and I'm trying to set up a database of all of our product lines. I'm new to Access (using 2010) but I've dabbled in VBA for Excel.

I've got a basic Form set up right now with the hopes that any user can come in and use this to look up an item and all information about it will be displayed for them. I've disabled everything in the form except a text box where they can enter in the item number. This pulls directly from the Item# field which is the primary key set to "no duplicates".

Right now the set up is that it automatically starts at the top of the list and they can either use the previous/next buttons to scroll through the products or they can enter the number they're looking for directly into the text box and the form will auto populate all infomation regarding that item.

What I'm having problems with is if they enter a number that isn't in the list, I'd like it to skip to the next available number in the list. For example the user enters 12005 into the text box but the closest item number to that is 12030. Is there any way to do this aside from changing the text box to a combo box and using it as a drop down list?

 

[kjv1611]

How does it work when they enter the value into the text box? Are you running code in the AfterUpdate event of the checkbox, or some other way?

Sure you can do that via VBA and/or a query

There are probably more than one way, but one way I can think of right away would be to do somethign to this extent in your code..

Private Sub txtProdID_BeforeUpdate(Cancel As Integer)
'Then use DCOUNT or DLLOOKUP to see if the value exists... if it exists, return the value... if not, return something like..
DMIN("thefield","thetable","thefield > [your entered value])
End Sub

PLEASE do not try to use that code... it won't work. It's just a thought getting into one direction.

I would work, rather, towards using at least one query. I found that when I could use a query to get what I needed instead of DCOUNT or DLOOKUP or any other D functions, performance was MUCH MUCH better.

I'll try to look at this more in depth if I remember and have time this weeekend... if not, maybe next week... by then, probably someone else will have something better.

 

[TheAceMan1]

How are ya Malagar . . .

Post the [blue]Record Source[/blue] of the form.
[ol][li] If the [blue]Record Source[/blue] is a table name, post the fields & data types of the table and identify primary keys.[/li]
[li]If the [blue]Record Source[/blue] is a query name, post the SQL of the query.[/li]
[li]If the [blue]Record Source[/blue] is an [blue]SQL[/blue] statement, post the SQL.[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Malagar]

This form is getting its info from a query. The query name is "Item Ti-Hi Query". Form is named "Item Ti-Hi Form". When the user enters an item number into the text box (referenced from "Item#" field in the query) and hit enter the form fills in the other fields accordingly.

I have code in the After Update event. It was put in automatically when I put the text box in.

="[Item#] = " & Str(Nz([Screen].[ActiveControl],0))

Please let me know if you need more info than this. Like I said before I'm very new to this stuff.

 

[TheAceMan1]

Malagar . . .

[blue] ... I'm very new to this stuff.[/blue]

OK ... lets see how you do.
[ol][li]Open [blue]Ti-Hi Query[/blue] in query design view.[/li]
[li]On the far left is the [blue]view[/blue] button.It has a dropdown arrow. Click the arrow and select [blue]SQL View[/blue]. This is the SQL of the query.[/li]
[li]Highlite, copy & paste the SQL into your next post.[/li]
[li]What version access are you using?[/li]
[li]Do not use spaces in your naming convention. If necessary, use the [blue]underscore[/blue].[/li]
[li]Prefix the type of object to its name. [blue]qryTiHi[/blue] is more easily readable than [blue]Ti-Hi Query[/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Malagar]

I'm using Access 2010. I've also taken your suggestion about renaming. It makes sense. Would I presume to not use spaces (replace with underscore) in the field names as well?

SQL is as follows:

SELECT tblTiHi.[Item#], tblTiHi.[Item Descriptions], tblTiHi.[Cases Per Layer], tblTiHi.[Layers Per Pallet], tblTiHi.[Cases Per Skid], tblTiHi.[Double Stackable]
FROM tblTiHi
ORDER BY tblTiHi.[Item#];

 

[TheAceMan1]

Malagar . . .

The easiest way to do this would be to have a form (in single view) who's [blue]Record Source[/blue] is based on . You'd have a combobox in the forms header based on the same query. In the combo the [blue]description[/blue] would show but you would goto the record selected via [blue]Item#[/blue]. In this way ... user can't type a wrong number and the textbox isn't needed.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Malagar]

You're saying that it would be easier to use a combo box and just have them use a drop down list? I had a drop down list before and can easily go back to it.

I would need it to show Item# though. As product recipes are modified to adhere to ever changing food laws the item# will change but description stays the same.

Thanks for your help AceMan.

 

[TheAceMan1]

Malagar . . .

Use the combobox wizard to setup the combo. Be sure to select the [blue]Find a record on my form based on the value I selected in my combobox[/blue] option. If you like you can [blue]Item#[/blue] & [blue]Description[/blue] show in the list. The choice is yours.


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Malagar]

I think I've figured out how this can be done but I'm not sure how to implement it. I'm sure it can be done if I use an IF...THEN statement in the AfterUpdate event in VBA.

I would check to see if the number entered is equal to a number on the list (pulled from [Query]![qryTiHi]![Item#][/color red]). If the number doesn't match anything in the list if would incrementally add 1 to the number and loop until it found the next number and stopped. Let me know if this looks close to right. I'm a little lost as far as what I should be coding in here.

Private Sub cboItem_1_AfterUpdate()

Dim intItemNumber as Integer

intItemNumber = [Forms]![frmTiHi]![cboItem#1]

[COLOR=green]' This is where I don't know how to match it in the list[/color green]
If intItemNumber <> [COLOR=red]???(Item in list)[/color red] THEN
       intItemNumber = intItemNumber + 1
ELSE
END IF 

End Sub

 

[PHV]

What about this ?

Private Sub cboItem_1_AfterUpdate()
Me.Recordset.FindFirst "[Item#]=" & Forms!frmTiHi![cboItem#1]
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Malagar]

Nothing. I stays with the last TRUE value in the list that was selected.

 

[PHV]

And this ?
Me.Recordset.FindFirst "[Item#][!]>[/!]=" & Forms!frmTiHi![cboItem#1]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Malagar]

You're the man PHV. Worked like a charm.