Search results

Good tip, I shall try to remember that for the future. I didn't even realize I had not declared lngWhite (not that it matters in this case as the combo is defaulted to white) so Option Explicit would have helped to notice that. Cheers.

That did it, thanks very much for your input Minty. Thanks also Gasman for your help :giggle:

I have looked at Select Case and come up with this Private Sub Form_Current() Dim lngOrange As Long, lngPink As Long, lngYellow As Long, lngGreen As Long Dim lngGrey As Long, lngRed As Long, lngPurple As Long, lngBeige As Long lngOrange = RGB(255, 192, 0) lngPink = RGB(255, 153, 204) lngYellow...

Hi Gasman, I've tried conditional formatting and I haven't been able to get it work, plus I am trying to learn VBA so thought the more I do it the more I'll learn. What expression should I use to get the conditional formatting to work? I've tried InStr([Order_Status],"On Schedule")>0 and...

Hi, Please can I have some VBA advice. I am writing some code so the Order_Status field back color changes depending on what text the field contains. This is what I have done so far: Private Sub Form_Current() Dim lngOrange As Long, lngPink As Long, lngYellow As Long, lngGreen As Long Dim...

Thanks Minty, that was a breeze! All working great now. Rookie error from me there adding a special character! I just removed it and renamed the field something sensible. Cheers!

I thought that too but I couldn't figure out where to put the # as I am using the second DMax as the criteria for the first so the # will then be in the expression of the second DMax syntax.

Hi Minty, Thanks for your suggestion. I have created a new query called UnionTablesTime_Query and added the fields together to form a field called 'Time & Date'. This is the syntax I have used but I am now getting an error. =DMax("Time & Date","UnionTablesTime_Query","[Order_Number] = '" &...

Hi all, I have created a union query (I have used a union query because my order form has multiple subforms) and I am then trying to use DMax in the order form to show the last modified date and time of the order. As shown in the attached image, I am trying to display the time highlighted in...

The table is called "Shipments" so I entered that where you said to in the code and it's solved the problem. Thanks so much once again.

Hi @arnelgp, I have run into a small issue with the code you wrote for me yesterday. When consecutive new records are added to the Shipments table on a new Customer_Order record the suffix on the Order_Shipment_Number doesn't move to the next letter. I have found the only way around it is to...

Thanks all for your advice :giggle:

Thanks so much arnelgp, that has got it working perfectly. Also thanks very much for your help Gasman, and for being patient with me! I really want to learn VBA to be able to write code like this. Do you learn that through experience or reference books? What advice can you give me on how to...

I am new to using VBA and have never troubleshooted code before, so I am not familiar with what you mean by 'walk through this code and inspect the variables". I see what you mean, the Order_Shipment_Number will never be the same as the Order_Number so they can't be compared. It's only the...

Its for the Shipments table. Yes it's a subform called Shipments_Subform within the Customer_Orders form.

That returned B in the Order_Shipment_Number field and 0 in the Delivery_Suffix field

I am trying to auto generate the Order_Shipment_Number. For every new record added to the Shipments table for each Order_Number, the number in the Delivery_Suffix field in the Shipments table should increase in increments of 1. This number is converted to a letter using ASCII and this letter...

I have been able to get this code to run: Private Sub Form_BeforeInsert(Cancel As Integer) iNextSuffix = Nz(DMax("Delivery_Suffix", "Shipments", "Order_Shipment_Number='" & Me.Order_Number & "'"), 0) + 1 Me.Order_Shipment_Number = Format(Me.Order_Shipment_Number, "00000") & Chr(64 + iNextSuffix)...

The code doesn't seem to be correct as it's not doing anything. It doesn't produce an error either. I have formatted the order numbers as short text as they required letters.

This is what I have written so far Private Sub Order_Shipment_Number_BeforeUpdate(Cancel As Integer) iNextSuffix = Nz(DMax("Delivery_Suffix", "Shipments", "Order_Shipment_Number='" & Forms!Customer_Orders!Order_Number& & "'"), 0) + 1 txtDeliveryCode = Format(Me.Order_Shipment_Number, "00000") &...