Best Way

[eveCalypso]

Good Day,

I am running in circles here.
What are the most common way to deal with Access ADP forms when it comes to Insert/Update screens?

I have complex and obviously less complex (like Stock Maintenance) screens to consider.
Should I let Access handle the traversing, inserting of new and updating of existing records with record selectors etc or
should I create my own toolbar(s) and general calling, updating and maintenance code myself?

Even though I am not an Access programmer, I have made the decision up front to work with ADP + a SQL back-end. I would like to use Stored Procedures etc., but the actual best way of dealing with the forms in Access is tripping me up. I keep finding that I use different methods for different forms - none of which I am even sure is the right way!

No good ADP books you can recommend?
EvE

 

[BuilderSpec]

Record selectors are not good.

Add a button to the form , let the wizard take over and select "record navigation" , first , prev etc are in there. You don't actually have to code it. it does that for you.. just give it a go !

"Record operations" will give you your delete / add functionality !

Regards

 

[eveCalypso]

Hello Again!

Those old grey ugly buttons
I assume you mean in the Footer of each form?

Thanks
EvE

 

[BuilderSpec]

Hi yourself...

Yes best in footer though actually doesn't matter, since they don't change their use / value between records they can go pretty much anywhere !

 

[BuilderSpec]

Alternatively you could create a menu with the record operations on it.

 

[eveCalypso]

Ok, here is another problem I encounter(ed):

I have a table with standard inputs for which I have written a query in my form, say:

select anID, aDescription, aCategory, anAmount from aTable

In my table I also have:
insertUser *
insertDate
modifiedUser *
modifiedDate *

Now, my form does not need these fields on it, but in order to let Access handle the insertion/modification of these fields (or the ones marked with * which I set), I need to include them as non-visible bound fields?

Is there a better way? I tried updating the recordset directly, but it did not work :-/

EvE

 

[BuilderSpec]

can you post the code that didn't work ? the recordset method ? Why didn;t it work.. what message did it throw ?

 

[eveCalypso]

Erm ok,
hang on - let me undo my "fixes"
Will send it shortly.
EvE

 

[eveCalypso]

Ok,
I removed the field I created for InsertUser and chaned the code on BeforeUpdate to the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.Recordset.Fields("InsertUser&quot.Value = currLoggedUserID
End If
End Sub

{checked and the value in the recordset is correct, but its not the value (clearly) Access writes to the DB }

This throws error: Can Not INSERT the value NULL into column "InsertUser" - etc etc.

If I create a bound field, say InsertUser, make it non-visible and then run

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
InsertUser = currLoggedUserID
End If
End Sub

it works!
EvE

 

[BuilderSpec]

Yes it will...

If your form is based on a query and that query originally was ...

select anID, aDescription, aCategory, anAmount from aTable

then the InsertUser was not part of that query and hence not part of the recordset the form is based on.

I am assuming that to get it to work you have included the field in the query , the form etc and is now working. It may have been enough to simply add it to the query but not show it on the form: it would now form part of the recordset.

Regards

Seems like you've fixed it anyway...

 

[eveCalypso]

Erm, yes and no

If I include insertUser in my query it is not enough ( I also thought it would be...) I still get that exact same error until I include a field on my form...
not good -

and I am sure it's me being a monkey - but I am not quite sure where I am losing my peanuts.

I have checked the value of the recordset and it is correct, so somewhow Access must use something else when it updates the DB - not the recordset I am changing.

Thank you anyway for listening to me ranting and raving on (yaaaa

http://wwwnnn

)!

EvE