transfer a colunm from one table to an other when # code 8 appears

[baycar]

I'm in the stell industry so fo excample lets say have 30 pcs of angles that tha are the same size each has a different code number to identify it now i've used one piece completely .In lieu of deleting this pc from stock I need to transfer it to an other table . I have a NO field in my table I was thinking of using the number 8 as search value to send the hole colunm to an other table form a query that is identical to the main tabl. how can this be done or is their an other way . Please help

 

[PaulBricker]

Do you enter the code number each time a piece is created? You could use something in the AfterUpdate event that says

If Left(CodeNumber,1) = 8 Then
Run an Append Query here
Else
End If

You don't really provide us with a lot of info so it's hard to say if this is even feasible. Post back with any questions.

Paul

 

[baycar]

Ok Paul here's a little more info It not Stell But Steal industry Ha! ha! The product code is year, month, and number of piece that came in that month. I've looked @ many Visual Basic code for this field but yet to make it work for me. but I do have a field called "No" , using numbers from 1 to 3 indicating a priority. think of adding the number 8 to indicate it is used and to remove from stock I'm not sure if a append query is for me.

 

[PaulBricker]

I assumed it was the Steel Industry because Google didn't have much on the Stell Industry. If you put an 8 in the Prioity field, it may mess up something there, and it sounds like the product code won't work either. What is the "Number of pieces" part of that code?
I would think you could create a separate table to hold the number 8 if you don't have anyplace else to store it but I don't know how you would apply it to the situation. Who enters that value? Is it arbitrary or does every piece run 1 - 8?


Paul

 

[baycar]

Perhaps still not clear I have One "field" called Baycar Code being 03-12-999 this code was typed in excel now i've transfered all of the inventory to access. 03 is the year 12 is the month 999 is the pc of steal coming into stock.This code number would be great to be set automatic ut i'm not familiar Visual Basics. I have another "field" called "NO" for now we're using the numbers from 1 to 3 to indecate its priority ,this is were i'm thingking of adding the value "8" to indicate nolonger available or out of stock. but I only want to transfer it to an other table and not delete it . We are using This Inventory Database at this time now i"m trying to tweak it up. Some of us hate typing???

 

[PaulBricker]

I understand about the typing. Adding an 8 to your priority field seems reasonable then. If you add the value 8, then all you would have to do is create an Append query that gathered records where the value of the priority field equaled 8. You could run this query automatically or behind the click of a button. The only issue I see then would be not to duplicate appended records to your Archive table. If you ran the Append query daily, you could filter it on the value 8 and todays date. That way, any records that were not dated today would not continue to be appended over and over.

Paul

 

[baycar]

Ok! created a a append query append to new table called used inventory with the same field as the main inventory table. In the Field called "NO" under append My choice was "used inventory.*" the asterisk is giving me an error can't match the field and "8 " under criteria does nothing I'm doing a lot of reading and trial and errors ... What I'm I doing wrong Please Help

 

[PaulBricker]

Can you post the SQL from your append query. You should not have this type of trouble with an append query. They are fairly straight forward.

Paul

 

[baycar]

Well it was easy I got it to work just before receiving your e-mail thanks . Now if I may is their an other way to make my year,month, number code to work with out Vusal Basics????? As I do have over 3000 pieces in stock at this time and the number were typed in so I don't want this affected. Thanks again Paul for the Append mistery

 

[PaulBricker]

I don't know of any way to make this happen without some VBA. Here's what you can do if you want. In the Current Event for the Form you can put this code.

If Me.NewRecord Then
Me.BaycarCode = Format(Date(),"yy&quot & "-" & Month(Date()) & "-"
Else
End If

Then in the Got Focus for the control BaycarCode you can put

Me.BaycarCode.SelStart = Len(BarcarCode) + 1

Then when you go to enter a new record you Barcar Code textbox will look like this
03-12-and the cursor will be blinking after the second -

Then you just enter the 999 value.

Paul

 

[baycar]

Looks good But room for error by typing in the last three digits. Do you have any secrets on how I can get a good VB source code for Baycar Code Field. and on how to install it I'm very green when it comes to VB codes and In where and when to install it. Thanks again for you help

 

[PaulBricker]

I will need to know a little more about the last three numbers. How are they selected? At some point, those numbers need to either be typed in or generated by Access. Does 999 relate to a specific material or is it just the next value in a three digit sequence?


Paul

 

[baycar]

next value in a three digit sequence. I'll use a PLate for example . the plate will be inputed into the database using the plate stock order entry form . a Copy of the Purchase Order will be sent to the receiving . A couple days later the order comes in the receiving dept. go to the receiving stock form this is a spread sheet form also were we would like and need the baycare code to work. One Ideal I was thingking was all steal pieces we receive must and have a heat code we receive from the manufacture maybe ones we add this heat code it can activate the baycar code?????

 

[PaulBricker]

Something like
Format(Date(),"yy&quot & "-" & Month(Date()) & "-" & Right(DLast("Baycar Code","TableName&quot,3)+1


Then in the After Update event you could just put
Me.[Baycar Code] = Format(Date(),"yy&quot & "-" & Month(Date()) & "-" & Right(DLast("Baycar Code","TableName&quot,3)+1


Test it on SAMPLE DATA please. Not on live data, to see if the DLast does what you need.

Paul

 

[baycar]

Will Do And Let you Know in the morning Thanks

 

[baycar]

Morning Paul
I've tryed your code no success .?? What data type should i be using @ this time it is text

 

[PaulBricker]

Post your expression as you are writing it. Also, you can break it down into parts to see which part seems to be failing.

Paul

 

[baycar]

Will keep on Trying ,thanks a million until next time my friend

 

[PaulBricker]

I understand. Good luck to you.

Paul