I am trying to get MichaelRed's aut 2

[perrymans]

I am trying to get MichaelRed's autonumber solution (faq700-184) working in one of my DB's.

My problem is that I can't debug becuse the following code:

'Update the Id value and (just for my own interest) the date/time stamp
With tblNewQiNum
.Edit
!QiNum = lngBigQiNum
!QiDateTime = Now()
.Update
End With
NewQI_Num = lngBigQiNum

I get the error that the Method or Data member is not found on the ".Edit" line.

What reference is needed to get this to work?

Thanks. Sean.

 

[JimHorton]

I'm not familiar with the rest of the code,
but the line "With tblNewQiNum" should be something like:
With rstNewNum

...or whatever the recordset that is being used, instead of the table name. I've seen MichaelReds stuff before and I doubt he would name a recordset with the table prefix, so I'm assuming that tblNewQiNum is the table's name, not the recordset object's name.

--jim

 

[perrymans]

I added recordset to it so it now reads "rs!tblNewQiNum".

However, tblNewQiNum was set as a recordset earlier with:

Dim tblNewQiNum As Recordset

Then assign the value with:

Set tblNewQiNum = MyDbs.OpenRecordset("tblNewQiNum", dbDenyRead)

So I got the compile to work, now I will work on the getting it to update me tables. Thanks. Sean.

 

[MichaelRed]

hmmmmmmmmmmmmmm, "Oh what a tangled web we weave when at first we attempt to" fix what we don't understand?

Certainly no certanties from the previous postings, but I would offer a modest wager that the additional delcaration doesn't contribute to a real soloution.

Again, just GUESSING, but the faq procedure was written long away and far ago, in the DAO age and has never been re-born into an ADO mold ...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[perrymans]

There is no doubt that I don't understand most of what you wrote in there. Please understand that I don't think it an error on your part at all, I am quite sure it is mine. As I said, I can see were tblNewQiNum was set as a recordset. I don't know what my machine is doing, but I am sure it has something to do with me not adjusting the code properly in a different place.

Now, I am not successful getting it to work. I tried creating a test form with my Key field and a command button, placing a call for your function behind the button. But I keep getting an Error 13: Type Mismatch fault, and the Key field is set to 0 on every record. I do have my field set up for Long Integer.

I am sure it boils down to not knowing how to properly call the Function. I am used to using a style like:

Call NewQI_Num("txtBNBNum&quot

but I assume I cannot here because NewQi_Num is declared As Long instead of a variable in the brackets. But all of my other variations haven't seemed to work.

Beleive me, I have faith in your code.

Thanks. Sean.

 

[MichaelRed]

hmmmmmmmmm ... mmmmmmmmm ... mmmmmmmmm

modifications have (obviously?) gone well beyond the routine posted in the faq. Two rather overt and obvious issues are:

1.[tab] You have added a calling argument, which was not previously present.

2.[tab]You are using a call statement to a function which is typed as a long, so the return value has no use / meaning.

To get help on your customization, you would need to post the relevant changes, and not (just) reference the faq, expecting anyone to know that there are (significant) changes, what they are and what you expect the changes to accomplish. In addition to the actual errors and / or problems.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[perrymans]

Actually, I have made no modifications what-so-ever other than adjusting for my recordset and that mysterious rs!tblNewQiNum I added.

I think you are mistaking me to be someone like you, good with the ol' VBA, well I'm not really. What I displayed above was one of my attempts on figuring out how it worked.

I have your code, I made the adjustments, but I don't know how to make it trigger. I though it was at the form level and assumed I had to Call it to get it to work (again, not too good on VBA yet).

What triggers this event to occur? It has to be requested somewhere doesn't it?

Thanks. Sean.

 

[MichaelRed]

Then, whence commeth the call argument? It is not in the original. What are you doing with the return value? To simply call a function without an assignment statement does not provide for your use of the return (vaalue).

It has been a while since I have actually used the routine, as I am not presently working (in multiuser environments), so I am not sure where I placed the call statement, it was probably in an update event, but that is more or less a choice based on the overall app/design. The more normal use would be ~~:

[tab]MyNewQiNum = NewQI_Num

NAd MyQiNum is used within the remainder of the procedure in the recordset as the PK value. For a bound form, it is 'convenient' to simply assign the value to a text box bound to the recorset field, as in:

Me.TxtPk = NewQI_Num

where:

"Me." Refers to the bound form of the recordset

"TxtPk" refer s to the specific control, and it's control source is the PK field of the recordset.

and NewQI_Num is, of course, the function which returns the actual value.

All of that being said, I would have some reservations in your being able to implement this procedure. I do not mean to be harsh, but the level of experience demonstrated in this thread does not indicate sufficient knowledge of programming in general to be involved in multiuser applications requiring routines of even this modest level of sophistication. At best, I would advise you to use the procedure in a test application before implementing it in a live (production) database. I would further strongly suggest that you either 'beg off' this particular assignment -at least for a while- and run (do not walk?) to the nearest book store and get a couple of the third party tutorials/reference books re Ms. A. and VB(A). Start a crash course in the two subjects (either self study or enrollment in formal classes). Once you feel like you could at least be able to follow the procedure (i.e. be able to generate comments/description re each line or block of the procedure), go back to the boss and let them know you are ready for the more challenging assignment.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places