Search results

This has developed a little, we would like to expand this feature a little more. NB: I know the table, form and field names are terrible, please bare with me. we have a form, JobRegisterBookingIn where the code above runs to check to see if the serial number already exists where the date...

thanks for that. Understand the information was shady :-) we will append to a temp table and use another query from there. thanks again :)

we have a calculated field in an append query Line_Cost which eventually creates a purchase order. We have to have a minimum order value of £1500.00, so, we need to total the values before running the append query. best way?

Morning all, I'm trying to get my head around something. I understand we should not store calculated fields. So on our purchase orders we have a price list, where the totals are calculated on the report. We have a form which shows historic purchase orders and the prices paid at the time. We...

Actually those are some of the nicer ones. :rolleyes:

excellent, final code: Private Sub Serial_No_AfterUpdate() Dim iCurrent As Integer iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No] = '" & [Serial No] & "' AND [Date Done] is Null") If iCurrent > 0 Then MsgBox "There is already an open order for this item." End If...

Thanks, I now have: Private Sub Serial_No_AfterUpdate() Dim iCurrent As Integer iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No]") = "" & [Serial No] & "AND [Date Done] is Null" If iCurrent > 0 Then MsgBox "There is already an open order for this item." End If End...

Thanks for that, I now get Compile error: Method or data member not found on me.txtnewserialnumber Is this looking for a text box called newserialnumber? Edit: I know about the very poor naming. Unfortunately the system was substantial before I got hold of it. Needless to say all the recent...

Hi there, thanks for the response, I've tried the code.. Private Sub Serial_No_AfterUpdate() Declare iCurrent as integer iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No]") = "" & Me.txtNewSerialNumber & "' AND [Date Done] is Null" If iCurrent > 0 Then MsgBox "There...

Hi all, We have a table, JobRegister that people enter information on using a form, FrmOrderEntry I would like to run a check when a serial number is entered in the field SerialNo to see if the same serial number exists where CompletionDate is null. this would stop duplicate open orders being...

Yes, the only reason originally to just use the part before the space is that the information entered after the space can be sporadic. When there is no space, I need to use the entire field. Apologies.

That doesn't give me an error when there isn't a space. But will not search for drawing numbers without a space. replacing the | with the full value of drawing No field lets the code search for the full value in Drawing No if there is no space in the field. IE drawing No = 12345 you...

When I use your code, it's throws a property not found on drawing numbers that contain a space...

Not sure why, but Like "*" & IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),0)=0,[Forms]![Job Register and Report Log]![Drawing No],Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No]," ")-1)) & "*" works...

I gathered that in the end. I'm struggling to understand the code, which is annoying me. Like "*" & IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),0)=0,"|",Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No],"...

Like "*" & IIf(Nz(InStr(),0)=0,"|",Left([Forms]![Job Register and Report Log]![Drawing No],InStr())) & "*" This now gives me a "typed incorrectly or too complex" error. It suggests using a variable. Looking at the code though, am I correct in thinking where a drawing number does not contain a...

Yes you did. But I have to clue where to start :-)

Like "*" & Left([Forms]![Job Register and Report Log]![Drawing No],(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "))-1) & "*" gives me what I want, as long as there is a space in the search term. If there is no space it errors on Property not found. How would I add a handler...

HI vbaInet. It's not always going to be done at order entry. It's likely to be done after the initial order entry. I don't need just the numeric. Ideally I need everything before the initial space. Like "*" & Left([Forms]![Job Register and Report Log]![Drawing No],(InStr(1,[Drawing...

Yes! where the drawing number entered onto the orders table is in the format : xxxx xx xx or x-xxxxx xx xxx or xxxxx/xxx/x xxx x I need everything before the first space. the field in quotes is "Drawing No"