One last try..

[pullmyefinger]

Need help to get past this pothole in my app.

I have a 1-Record Table called SEQ (sequence number table).

There are 8 fields corresponding to 8 different locations and are named as SEQBI, SEQLH, SEQPL, etc.

-------------------------------
The thing about this is since there are 8 locations I cannot and will not hard code this for every location. The reason is that the locations are stored in an Optiongroup and I have to let them pick any location they want at any time.

The person entering the data should be able to pick any location to add equipment to, get the Next Sequence number for that location and Add that piece of equipment to the Location table with the Unique Sequence Number.

The end result would be a report by location of all items in that location amongst other things.

"If SEQPL has already used sequence numbers 1,2 and 3, then #1 might be a router, #2 might be a Sawzall, #3 could be a cd player. The current value in the SEQPL field would be 3, and when the next item gets added to the PL location, the app would:

1. open a connection to the database/tables/fields/values etc..

2. after the user picks the right location (this part works) via an option group, a variable is created containing the name of the correct field in the SEQ Table as:

Dim Seqvar="SEQ"

If the var loctext="BI" from the options group, Then the concatenated
variable fldname (fldname=seqvar & loctext) would, and does have the value "SEQBI".

"SEQBI" is a field in the SEQ table and will need to be accessed for its current value (sequence number),

THEN store that value to a Variable (THIS IS WHAT I DON'T KNOW HOW TO DO - KEEP GETTING R/T ERROR 424 SAYING MY EXPRESSION IS NOT A VALID OBJECT. I AM GUESSING THAT IT IS COMPLAINING ABOUT HOW I DEFINED THE SEQBI FIELD IN THE SEQ TABLE OF THE 1 AND ONLY RECORD)

THEN, ONCE THAT WORKS, I INCREMENT THE CREATED VARIABLE BY 1 FOR THE NEXT SEQUENCE NUMBER (X=Y+1)

THEN WRITE THE X VARIABLE'S VALUE BACK TO THE SEQBI FIELD IN THE SEQ TABLE. THIS WOULD MEAN THAT THERE ARE NOW 4 ITEMS AT LOCATION "BI" ONCE THE OTHER LOCATION TABLE IS FILLED IN WITH THE NECESSARY INFO.

---------------------------------

IN SUMMARY, HOW DO I:


TAKE A TABLE CALLED SEQ WITH A FIELD CALLED SEQ?? (WHATEVER), STORE ITS CURRENT VALUE IN A VARIABLE "Y", INCREMENT THAT VALUE BY 1 TO VARIABLE "x" AS X=Y+1, THEN WRITE THE VALUE OF THE VARIABLE X BACK TO THE SEQ?? FIELD??????


THIS PROCESS WOULD REPEAT FOR ANY LOCATION/CORRESPONDING FIELD IN THE SEQ TABLE AND ANY NUMBER OF ITEMS.

-----------------------------------------------------

Now I know what a 10-year old feels like being blindfolded and trying to hit a swinging pinata..


































Each field above will store a sequence number that corresponds to the next number to be used for tracking items.

As an example, let's say the SEQPL field currently has a value of 0, meaning there is no equipment at that location yet.

 

[John Big Booty]

Have a look at the Dmax() +1

Perhaps the attached sample will help.

 

[Uncle Gizmo]

STORE ITS CURRENT VALUE

By "STORE ITS CURRENT VALUE" Do you mean maximum value?

 

[pullmyefinger]

no. sorry for the ranting and bitching earlier. i hate access cuz I was brought up on R:BASE System V, c.1986, and learned the right way to write dbf code. yes, i know this is object-oriented programming and the other is dos stuff but I got spoiled on good software early (ANSI 68 COBOL for one).

People don't seem to understand that there was Life Before And After Microshaft. I guess I have to lay in this bed that I made for a while..

Anyway, e.g., table SEQ has fields corresponding to locations/buildings/whatever. These locations have equipment that needs to be recorded. The reason for each field, such as SEQLK, is that "LK" is a location/building, so the field SEQLK has an integer value that represents the number of items cataloged/inventoried/recorded so far.

lol, right now all those fields' values are zero cuz I can't figure out how to get this to work.

Again, let's say that location "LK" (field SEQLK in Table SEQ) has a value of 3. That would mean that there are 3 items at that location that have ALREADY been recorded/inventoried/cataloged.

What I keep forgetting to tell everyone in this forum is that #1 might be a toaster, #2 could be a wood lathe and #3 might be a microwave. The Important Part of this is that I have a form where a user just picks the right location from an option group and then the Next Sequence number would popup for them in the form automatically.

So, if they picked location LK the first 3 times the current value in the field SEQLK would be 3. If they then picked location "BI" and there were no items recorded there then the current value of the field SEQBI would be Zero.


When they added the first item to location BI, the program would read the seq table and the corresponding SEQBI field to get the current value (0), store that number to a Variable (X), Increment it by 1 to another variable (Y) as in Y=X+1.

At this point var Y's value would be 1. The 1 would represent an item recorded at location BI, BUT that 1 is in a VARIABLE and NOT Written back to the SEQBI field in the SEQ Table.

Until that (sequence number) 1 is Written back to the SEQ table in the SEQBI field for all intents and purposes the Item does not exist.

---------------------------------
Let's put it this way: The SEQ table contains ONE record with 8 fields and will always only need 1 record. The value stored in each field IS the "maximum" if you have to look at it that way, but there is only one record in that table. SO, 6 of one, a half dozen of the other....

If i can get to read the current value of the Current (and only) Record from the proper field, that IS the "maximum value", so there is no need for DMAX or DLOOKUP or D-Anything because there is only ONE record in the table.

My guess is that if you all are trying to tell me something like:

Dim currentvalu, nextvalu as integer
Dim fldname,loctext, seqvar as String

seqvar="SEQ"
loctext="LK" 'location text, such as LK, BI, BH
fldname=seqvar & loctext 'fldname BECOMES the actual field "SEQBI"

pseudocode only for DMAX cuz I don't know the syntax yet:

currvalu=DMAX(fldname) 'access needs to look up the largest value in the SEQBI field and store it in the variable currvalu.



currentvalu = "DMAX(





Capiche? Sounds like most of you are thinking one record per item and that is not the way this table is written.

This is not a traditionally-written application so traditional thinking goes out the window..

Again, the people that would be doing the data entry know nothing about computers, so this app needs to do everything for them Automatically except type in the item descriptions.

I don't know how else to explain this. I feel like Lloyd Bridges in the movie AIRPLANE! when he said "Looks like I picked the wrong week to Quit Drinking"...

Thanks to all.

 

[pullmyefinger]

ok, Thanks. Please read what i wrote to Gizmo as an explanation of what i really need this to do. This is not written traditionally as 1 record per item IN THIS TABLE; that comes in another table and is the easy part.

True or Not?: If you have a One Record table with integer datatype fields in it, there is no difference between doing a DMAX+1 vs. finding the right field in that 1 record of that table and storing that integer value to a Variable????

Same Difference??

If you would tell me that you can store the correctly-written DMAX+1 function's result to an Integer variable, I understand that.

 

[John Big Booty]

...
Let's put it this way: The SEQ table contains ONE record with 8 fields and will always only need 1 record. The value stored in each field IS the "maximum" if you have to look at it that way, but there is only one record in that table. SO, 6 of one, a half dozen of the other....

...

Why is you table only going to ever have one record in it

 

[pullmyefinger]

John: this is all the stuff so far. If you go down to the 3 rows of asterisks (*'s)
you will find the dmax function you suggested and the R/T Error it is giving me.

I understand that Fn, been using it for decades. Thank You, big help, very simple.

-----------------------------------------------
WHY only one record in the SEQ table?? The explanation would confuse you more.

I don't need to tell you this, but remember that the DMAX will work regardless of the number of records in any table. The critical thing here is being able to store that value in a variable.

Please pay particular attention to the concatenated variable "fldname" where
fldname=seqvar & loctext

(as an example, if seqvar="Seq" (it does) and loctext (location text) = "BI",
the concatenation of seqvar & loctext becomes "SeqBI", and that IS a real, valid, actual field name in the SEQ Table).

Reason?? The only way I know to explain it is the more Automatic this app is, the less the end users have to do, the less confusing it will be for them. I am pretty much forced into that corner programatically.




I am trying to use "fldname" to represent an actual field name in the table SEQ, and I am hoping the VB will interpret it correctly. "fldname" is a VARiable and not an actual field in the SEQ Table





SUBS BELOW find the rows of stars and you will find the DMAX Fn


Private Sub Form_Load()
' Declare Connection and Recordset Variables
Dim rstSEQ As ADODB.Recordset
Dim Conn As ADODB.Connection
Dim opentbl, connstring As String
Dim fld As ADODB.Field
' OPEN CONNECTION

Set Conn = New ADODB.Connection

connstring = "Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source=C:\Users\me\Documents\Access Apps\SandsNet.mdb"
Conn.Open connstring


' Open Recordset with SEQ Table????????
Set rstSEQ = New ADODB.Recordset

opentbl = "SEQ"

rstSEQ.Open opentbl, connstring, , , adCmdTable

' REMEMBER fld.name and fld.value. You will need to use these later in the Click Event



End Sub
Private Sub LocFrame_Click()
' Define Variables
Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb

Dim cval, nval As Integer
Dim loctext, resp, seqvar, sqlstr, fldname As String
seqvar = "Seq"

' IMPORTANT!! "Me.Controls" is the current object, "LocFrame" is the Name of the Control,
' AND ".VALUE" is the sequential value of the option buttons (1,2,3,etc)
' of the Locations from top to bottom. It works!!
' YOU DON'T HAVE TO BIND THIS STUPID THING TO A FIELD VALUE
' LocOptVal = Me.Controls("LocFrame").Value THIS DOESN'T WORK ANYMORE;
' USE THE VALUE OF THE OPTION GROUP AND NOT THE BUTTONS
' Convert Option Buttons to the right variable name in SEQ Table

Select Case Forms!EQPT!LocFrame.Value ' MUST DO THIS THIS WAY!!! NOTHING ELSE WORKS
Case 1
loctext = "SB"
Case 2
loctext = "PG"
Case 3
loctext = "PL"
Case 4
loctext = "SR"
Case 5
loctext = "LH"
Case 6
loctext = "BH"
Case 7
loctext = "BO"
Case 8
loctext = "BI"

End Select

fldname = seqvar & loctext 'field name in SEQ Table
resp = MsgBox("You clicked on " & fldname)
' Forms!EQPT.RecordSource = "SEQ"
'sqlstr = "SELECT * FROM SEQ WHERE Recno=1"
'Set qdf = dbs.CreateQueryDef("", sqlstr)
'Debug.Print "Field name " & fldname & " Value is " & fld.Value


**********************************
**********************************
**********************************


cval = DMax("[fldname]", "SEQ") ' ERROR 2001 - You cancelled the requested operation
nval = cval + 1
resp = MsgBox("Current , New " & cval & " , " & nval)

End Sub








End Sub

 

[John Big Booty]

...

I don't need to tell you this...

That is entirely true, there is no compunction for you to share anything with us, however it might help in our understand of exactly what it is you are trying to achieve.

I will however share this link with you for your edification.

 

[pullmyefinger]

Capiche. Thank You John.

 

[pullmyefinger]

To the person from my last deleted thread that wrote DEMO.ZIP:

Thank You Very Much. That code and data structures are so streamlined

vs. what I wrote that I am embarrassed.

Good, that means I have tons more to learn.

Thanks to ALL, I learned more than I realized I needed.

 

[gemma-the-husky]

this sort of syntax should work

dlookup("SEQ" & extension, "SEQtable")

for extension "BI" it resolves to

dlookup("SEQBI", "SEQtable")

... as there is only one row, you don't need another parameter in the dlookup

------------
you can update it with

strg = "update SEQtable set SEQ" & extension & " = " & newvalue {for a number} or
strg = "update SEQtable set SEQ" & extension & " = " & chr(34) & newvalue& chr(34) {for a string}

then
currentdb.execute strg {to actually process the SQL string}

 

[boblarson]

Pullmyefinger:

Please do not delete posts when you have an answer. That isn't how forums work. They exist here as a resource for others as well and if you delete your thread then others can't benefit from the help you've received.

I have undeleted the thread.

Bob Larson
Super Moderator
Access World Forums