Fill Field from table with one entry, stumped. 2

[hughed00]

We are creating a form that "creates" an EAN-13 code when requested. These codes have a fixed 7 digits(basically a license)followed by 6 digits you can do anything you want with.

We have decided to use the 5 digits following the license as the ones which will indicate unique assignment with the 6th as a check digit of the entire 13.

We figured it would be fairly simple to write a macro which would retrieve '99999' from a table that contains only this one 5 digit number, insert it in the form, then decrement by one the '99999' to '99998' (and so on for each new code).

We can't seem to figure out how to retrieve the number from the table in Access. I can't seem to find anything that would allow me to point to the one populated cell in the table and bring it in to my form.

I figure this is something stupid, but 3 other people have said "oh that should be easy" and then failed as well. I guess we are a bunch of idiots.

Please help us as we are obviously Access challenged in my office....

 

[jebry]

Hi!

Try this code:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

YourUnboundTextBox.Value = rst!YourFieldName
rst!YourFieldName = rst!YourFieldName - 1

Set rst = Nothing

hth
Jeff Bridgham
bridgham@purdue.edu

 

[BoxHead]

Jeff,

I'm curious (everyone says so).

Could this be done with a public function in its own module?

Public Function MyIncValue() as Integer

    nz(MyIncValue) = MyIncValue + 1

End Function

And then call the function and calculate a value when the new record is saved:

     MyIncVal
     MyFiveDigitVal = 99999 - MyIncVal()

Or... would MyIncVal reset to 0 each time the app was opened? I apologize if it's a patently stupid question but I'm just beginning to work with these Functions and I'm not sure how far they can reach.


Thanks,
John

Use what you have,
Learn what you can,
Create what you need.

 

[Robertd]

Hi,

well the function would need to initialize the value between calls, unless you made the function to hold the value between calls using a Static type, but that would need to be initialized when first called, then the value could be used during the running of the program.

The other problem here is that the value needs to be available in a multi user situation such that all programs using the database can keep track of the last issued value.

Now i don't use macros so how you would do that i don't know. i would use vba. the next problem is that you will need a table to hold the currently issued number or possibly a table to hold a list of numbers and included in the table might be be IssueNr, Issued perhapes some other details related o the issue of a number? where the issued field could be a Y/N you could eloborate from that.

look for the max value where issued is = No the macro would need to extract the value and set the issued field to Yes.

in vba use the dmax function.
or to build the table as numbers are issued, use the dmin retrieve the value subtract 1 from it and add a record. you then have a number to use in your scheem.

hth

Robert Dwyer
rdwyer@orion-online.com.au

 

[hughed00]

Jebry's code worked fine except it choked on the line

#Set rst = Nothing


I am continuing to dig and finding new ways to do stuff (I am a novice to Access but not SQL). I am so frustrated since Access makes things so 'easy' I feel like a fool when I can't figure out how to make the out of the box stuff do what I want without resorting to VBA or something of that ilk.

I too was wondering about a similar approach as Boxhead using macros (this is just on step in the conclusion of the form which I would prefer stays out of sight of the users).

So, thanks all that contributed and any more thoughts are greatly appreciated!!!

 

[jebry]

Hi All!

To John: You are correct the public function will always return the same number. If hughed00 needed to get this value from a variety of forms, then I would recommend a public function which ran code similar to what I posted.

To Robert: There is nothing wrong with using the DMin function, but it sounded like hughed already had a table set up so I ran with that. To use the DMin the 5-digit value would need to be stored as a separate field(most likely that would be a good idea anyway) or you would need to do some conversions to decrement the value and then re-store it as a string.

To hughed00: It is definitely a good idea to set the object to nothing. The only thing I could see in what you posted is the # shouldn't be there, but I suspect that is just a typo. If you're not getting that to work properly, you can post your code and we can see if we can figure it out.

hth Jeff Bridgham
bridgham@purdue.edu

 

[hughed00]

Big, DOH!

The resetting to 0 worked fine. It was the decrementing code;

rst!AssignedNumber = rst!AssignedNumber - 1

That did not work (the table is AssignedNumber and so is the field).

You are correct in that I did create a table with that one entry AssignedNumber.AssignedNumber = 99999 (only one record/cell in the table).

As to the decrementing, it isn't important that it be in this chunk of code anyhow, so I can get it done someway, if not here.

As to how this is being used, to answer some of the conjecture above as to what need to access this, this is simply a data entry form where specifics about a location are recorded, then the EAN-13 generated for that site. All of the data is then put into a table for use in creating routing tables for our messaging system.

The app is used lightly and by one person at a time. Over 3 years we have only generated about 2000 of these in the legacy app. It is just essential that when we do assign a number to a site, that it is unique. That is where the 5 digit number we are manipulating comes in.

Thanks for everyones assistance.

I am a complete Access novice and am learning as I go. On top of that, the project to develop this database is only one of many things I am working on (read do a little here and there when I have time, no time to really learn well) and the only one that requires any more tech knowledge than how to use Notes and Powerpoint to create presentations. I don't do hands on technical stuff much anymore.