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:
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:
DoCmd.OpenForm "lgdCblSeq_subform", acFormDS, acLast, , acFormReadOnly
DoCmd.GoToRecord , , acLast
[lgdCblSeq_subform]![filler] = [lgdCblSeq_subform]![cblNext] 'This generates new record with unique number
cblNew = [lgdCblSeq_subform]![cblNext] 'Is this copying the number or not?
gtNum = cblNew
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.