Full Version: Copying Value from Subform to Form through VBA
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
danielleoh
This is kind of tricky to explain, but here's the gist:
I have a form where the User is creating new part numbers for cables in a large power assembly. Sometimes the cables have unique numbers assigned to them and sometimes a group will share the same number but will have a different letter suffix per each individual cable. This part was simple to set up by mating the number and suffix to build the primary key.
What I am trying to do now is to use a single button to get the next available number. This took a little head-scratching, but I am almost there. I use a function to open a subform, tied to a table with an autonumbered field and another field called "Filler" (because you can't have a single field that autonumbers, and still create new records, apparently).
So my button calls:
Dim cBl
cBl = gtNum()
Form_tblCables.cblSeq.Value = cBl
Before I show my function gtNum(), let me say that if I force gtNum to just say something like:
gtNum = 11
Then the routine runs and the value in the form changes to 11. So the basic structure of the function is apparently correct (I have some C experience and practically no VBA experience, so wasn't sure about this).
Here's my gtNum function:
Function gtNum()
Dim cblNew
DoCmd.OpenForm "lgdCblSeq_subform", acFormDS, acLast, , acFormReadOnly
Refresh
DoCmd.GoToRecord , , acLast
[lgdCblSeq_subform]![filler] = [lgdCblSeq_subform]![cblNext] 'This generates new record with unique number
Refresh
cblNew = [lgdCblSeq_subform]![cblNext] 'Is this copying the number or not?
gtNum = cblNew
DoCmd.Close
End Function
The behavior is good, and it almost works! l open the form, the new record is created and a new number is generated. This number is apparently returned, but fails to print into Form_tblCables.cblSeq.Value.
I have tried a variety of things, including changing gtNum to just read gtNum = 1; this will return the value and works.
I also tried forcing all referenced variables and the function itself to read "As Long" but kept getting errors about there being an invalid use of NULL.
Anybody see anything? I sort of suspect that it will turn out to be something simple; new to this and keep making the simple mistakes, it seems.
adamsherring
Have you tried using the DMax function to get the highest value (next available number)? That may save you a lot of trouble with opening subforms and such.
dam
danielleoh
Thanks, Adam. BTW, that loud slapping sound you just heard was me, smacking my own forehead...
Danielle
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.