Select Higher ID

[edgarchado]

Hi all,

I have a an order form. Each order has it's own ID.

On of the fileds in the form is company. When I select a company from a list, it automatically fills other list fileds with data from older orders from that particular company.

If that company had only one previous order it only fills the other fileds with one line. If it had more than one order it fills the fileds with all the previous information.

Is there a way so that it only shows the last order? Maybe using the order number which is an autonumber?

Thanks
Edgar.

 

[hneal98]

If you have an order date, you can find the max order date.

 

[edgarchado]

hneal98,

I do have an order date. How would I find the highest date?

Thanks.

 

[caoamo]

hneal98,

Have you tried DMax()?

try this code in the OnClick or AfterUpdate event of the company field:

Dim loDate as Date
[COLOR=green]
'Get the company's last order date
[/color]
loDate = DMax("[YourDateField]", "YourOrderTable", "[company] = '" & Me.companyList.value & "'")

'Set rowsource to record matching that date
Me.RowSource = "SELECT * FROM yourOrderTable WHERE company = '" & Me.companyList.value & "' AND yourDateField = #" & loDate & "#;"
Me.Requery

I'm no coding expert, so you might have to modify it a little, but that should get you close.

 

[hneal98]

caoamo, why are you asking me? You should be asking edgarchado.

 

[edgarchado]

caoamo,

it is working as I need it to...

The problem I have now is that if I have several records with the same date it selects the first one (the oldest) and not the last one of the day (the newest)

Any ideas how to solve this???

Thanks

 

[caoamo]

hneal98 - whoops read the wrong name when typing my reply...

edgarchado,

For the future, you could switch the order date field to a long date/time format, that way time of day is included in the DMAX lookup. However, that won't solve the problem for records already in the table as they will all be time stamped the same.

You could use the DMAX to find the highest order number, however there are some risks involved with using the autonumber to look up a record. I would be certain that there is no chance of your DB using old deleted order ID's when creating new records....then its possible.

try this

Dim loID as Integer

'Get the company's last order ID
loID= DMax("[orderID]", "YourOrderTable", "[company] = '" & Me.companyList.value & "'")

'Set rowsource to record matching that date
Me.RowSource = "SELECT * FROM yourOrderTable WHERE company = '" & Me.companyList.value & "' AND orderID = #" & loID & "#;"
Me.Requery

I tend to be leary of this method, especially if your users have update, delete and add priveledges....be sure that you autonumber always increments to a new number for this to work.

 

[edgarchado]

I will give it a try. thanks