Place a variable from a module into a field on a form 3

[67peshawar294]

Gentlemen,
I have created a function to transpose the first 6 characters in a upc code to letters and add the last 6 as numbers. I cannot seem to figure out how to put this variable in a field on a form so I can process the information.
Any help would be much appreicated.

jpeneh10

 

[Rohdem]

Set the control source of a text box equal to your function

=functionname() Mike Rohde
rohdem@marshallengines.com

 

[67peshawar294]

I tried that first.
The function name is alphaone()
The body of the code is a for-next changing the first six numbers to text;
"If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "0" Then upca(G) = "A""
and so on for g=1 to 6
I then set the variable,upcnew to; upca(1)&upca(2)... to 6.
and set alphatwo = upcnew & Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], 7, 6).
alphatwo is the variable I want to add to the text box.
When I place a "debug.print alphatwo" in the code it prints just the way I want it
I am not very structured in my code and VB is a real challenge for me, I don't know if I have a mental blank or if I am just a mental case...
Thanks again
jpeneh10

 

[ghubbell]

Hi jpeneh10,
Change your function to as below:

Function AlphaOne() As String

Now your finished result of "alphatwo" can be replaced with
the new string variable we just called "AlphaOne"
Replace all your "upcnew" and "alphatwo" to read "AlphaOne"

Look it over and see what its doing. The function is in its self returning "AlphaOne" as a string value. You can keep reusing it throughout the function 'till you are ready to send it out to the field as described above. Read up on functions and the many ways you can work with them. Once you get a good grasp on them you'll be cookin' ! Good Luck,


Gord
ghubbell@total.net

 

[Rohdem]

Gord is right, you usually want to assign a value to the name of your function somewhere inside of the function. Then when you call the function, the value that you assign to it is returned to where ever you called the function from (i.e. text box control, query, or code) Mike Rohde
rohdem@marshallengines.com

 

[67peshawar294]

Hi guys,
What the heck am I doing wrong? I am just about to give up. I still can't make this thing work. I just keep getting a "#name?" in the text box.
the entire code is listed and I am really getting frustrated.

Option Compare Database
Option Explicit
Function ALPHAONE() As String
Dim G As Single
Dim alphatwo As String
Dim upca(6)
For G = 1 To 6
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "0" Then upca(G) = "A"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "1" Then upca(G) = "B"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "2" Then upca(G) = "C"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "3" Then upca(G) = "D"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "4" Then upca(G) = "E"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "5" Then upca(G) = "F"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "6" Then upca(G) = "G"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "7" Then upca(G) = "H"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "8" Then upca(G) = "I"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "9" Then upca(G) = "J"
Next G
alphatwo = upca(1) & upca(2) & upca(3) & upca(4) & upca(5) & upca(6)
ALPHAONE = alphatwo & Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], 7, 6)
ALPHAONE = Chr(91) & Left$(ALPHAONE, 6) & Chr(47) & Right$(ALPHAONE, 6) & Chr(93)
Debug.Print ALPHAONE
End Function

I have =ALPHAONE() in the control source of the text box.
The chr() are start and stop characters for the upc font.
so that 764724069780 returns [HGEHCE/069780](copied from the debug window)
I appreciate all the help and advice you have given.
Thanks
jpeneh10
Jim

 

[Rohdem]

The problem doesn't seem to be with the code itself, but with the call to the function. Is the function located in a separate module, or is it located within the module of the form where you are trying to display the data? It seems like your textbox can't find the function, thus the #name? error. Try creating a new module, paste the code into it, save it, then call it from your form. Mike Rohde
rohdem@marshallengines.com

 

[ghubbell]

Ditto to Mike's note Jim, & by the way: never give up! It's just a computer! You can still loose "alphatwo" as below. We'll just "recycle" alphaone.

Function ALPHAONE() As String
Dim G As Single
Dim upca(6)
For G = 1 To 6
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "0" Then upca(G) = "A"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "1" Then upca(G) = "B"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "2" Then upca(G) = "C"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "3" Then upca(G) = "D"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "4" Then upca(G) = "E"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "5" Then upca(G) = "F"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "6" Then upca(G) = "G"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "7" Then upca(G) = "H"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "8" Then upca(G) = "I"
If (Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], G, 1)) = "9" Then upca(G) = "J"
Next G
ALPHAONE = upca(1) & upca(2) & upca(3) & upca(4) & upca(5) & upca(6)
ALPHAONE = ALPHAONE & Mid$(Forms![Copy of print upc symbols]![UPC SYMBOL], 7, 6)
ALPHAONE = Chr(91) & Left$(ALPHAONE, 6) & Chr(47) & Right$(ALPHAONE, 6) & Chr(93)
End Function

Don't let it win......
Gord
ghubbell@total.net

 

[67peshawar294]

Gord and Mike,
I finally solved the major problem. I copied the function and placed it in an event (On Enter). It works fine, not as well as I would like, I have to click on another field to get it to advance to the next record, but that is a minor inconvienence. Thanks for all the support and encouragement.
Jpeneh10
Jim Peneton
jpen_10@hotmail.com

 

[ghubbell]

Hi Jim,
Glad to see it's working almost to spec. You might try to add this line to the after update event of your field:

Me.Requery

I think this might take care of the last little bit. I don't know your form setup but if it goes in to a loop, press CTRL+BREAK to stop, and try the same command in another event. Take care, Gord
ghubbell@total.net

 

[67peshawar294]

Hi Gord and Mike,
back again with the same module! I finally got it to work the way it should [I changed the name of the module and used the expression builder function alphaone()] but now I can't get the result of alphaone to append to the table. The result shows in form view and in datasheet view but does not show in the query or in the table after I run the append query to another table. The other fields that I enter from the keyboard append OK.
Thanks, Jim

 

[ghubbell]

Hi Jim! Progress is progress! I think you'll have to paste your append query in here for a closer inspection...but first...you're running this with your form open? (You have to right?!) You say the result shows in form view...Where? in a textbox perhaps?! Here's one way to do it...Append the value in the textbox to your table: in the "Append to" row under the appropriate field: forms!nameofform!nameoftextbox. or something like should do it. ;-) Gord
ghubbell@total.net

 

[67peshawar294]

Yes the form is open. It doesn't appear when I view the "ENTER NEW FRAMES" table either. I enter data through a form to a separate table(enter new frames) and and then append it to a different table(frames). Here is the SQL for the query. I tried referencing it as forms! etc. but no luck. The field in question is " Frames [Check]" and "ENTER NEW FRAMES [CHECK]"
SQL:
INSERT INTO FRAMES ( SUPPLIER, FRNAME, NHERE, STYLE, MATERIAL, MFG, WHPR, RETPR, AMOUNT, DATEIN, CHECK, [NUM SOLD], [UPC SYMBOL] )
SELECT DISTINCTROW [ENTER NEW FRAMES].SUPPLIER, [ENTER NEW FRAMES].FRNAME, [ENTER NEW FRAMES].NHERE, [ENTER NEW FRAMES].STYLE, [ENTER NEW FRAMES].MATERIAL, [ENTER NEW FRAMES].MFG, [ENTER NEW FRAMES].WHPR, [ENTER NEW FRAMES].RETPR, [ENTER NEW FRAMES].AMOUNT, [ENTER NEW FRAMES].DATEIN, [ENTER NEW FRAMES].CHECK, [ENTER NEW FRAMES].[NUM SOLD], [ENTER NEW FRAMES].[UPC SYMBOL]
FROM [ENTER NEW FRAMES];

Jim

 

[ghubbell]

Jim, If you leave the form open, open the query in design view, then shift to datasheet view (don't run it with the ! button) what do you get as a result? Gord
ghubbell@total.net

 

[67peshawar294]

I get the same thing. All entries I make from the keyboard show up, the [check] is blank. No pun intended...

jim

 

[ghubbell]

Sorry..I'm taking a step back here...{fine pun btw...need humor as fuel!)

If the field on your form is bound or unbound and by punching in some data it returns a value using your function in to the same unbound field, you will have to push this returned value in to the field that is bound to your first table:

If it's unbound, after you run your function, do something like: me![check]=me![nameofunboundfield]

Bound or unbound add this after function, and the stuff above if required:

DoCmd.runCommandAccmdSaveRecord

I think. I believe your record is sitting there "dirty" one way or the other: not saved to the first table yet. Save it like this if the rest of your records data will allow. Stop there and see if it made it to the first table. If its there now run the append query. Sorry I didn't fully absorb that you were installing the result in one table then shifting to another. We'll get it!



Gord
ghubbell@total.net

 

[67peshawar294]

Hi Gord,
I am going to give up doing it this way. I created several more modules and renamed to each form. Works like a charm.
I hope it's not the chicken way out or that it isn't "bad Coding."
I really want to thank you guys for the help. This is a fantastic site and you all deserve a round of applause.

Jim

 

[ghubbell]

Jim! What ever you do to make it go is fine. If you have a chance though and would like to send it over I'd be glad to scope it out and see what I'm missing. I just can't quit! Your call, your choice... my treat! Gord
ghubbell@total.net