simon_knee
asked on
ADP/SQL Server, whats the best new record on form method.
Hi,
Upsized my 2000 mdb to SQL server and an ADP.
I had a lot of code manipulation to get it all working but I am just about done.
I want to make sure I head down the best route for creating new records in this environment.
The forms serve both viewing and data input of records, i.e. I don't use wizards or popups when data is to be editted or a new record created. The user gets to do it all on the one page. I have buttons that switch the form between "Edit" and "Done" that enable and disable various controls to prevent accidental editting or navigation whilst editting.
Then I have a "New" Button that generally...
Prompt for name of new thing from a popup with combolist
Ask for a few more items of essential info
Make checks against existing data (searching clones with ADO)
Create new record (Docmd.GotoRecord , , acNewRec)
Populate fields on record with information collected above.
Leave user to fill in extra info on form and subforms.
Oh and the user gets an undo button (that sometimes works, sometimes doesn't).
Docmd.GotoRecord , , acNewRec doesn't seem like a very good method anymore now I have an SQL back end.
For instance a field that is =mnManufacturer + ' ' + eqMake doesn't display this sum any more as this new record is in progress.
Do I need an update or save command at some stage?
This is one area where I never felt very in control of where my data actually was. Am even less sure now I am using sql server!
Maybe use ADO to create new records.
Or should I use SQL commands directly to INSERT data.
Or something else.
I am aware that there is a issue with the time that autonumbers and prmary keys are created and when they are available for use by code.
The undo feature is nice but not essential (would be nice to have a bit more consistancy).
I want to head down the right path now rather than revisit it all later.
So what method do you lot recommend?
Simon
Upsized my 2000 mdb to SQL server and an ADP.
I had a lot of code manipulation to get it all working but I am just about done.
I want to make sure I head down the best route for creating new records in this environment.
The forms serve both viewing and data input of records, i.e. I don't use wizards or popups when data is to be editted or a new record created. The user gets to do it all on the one page. I have buttons that switch the form between "Edit" and "Done" that enable and disable various controls to prevent accidental editting or navigation whilst editting.
Then I have a "New" Button that generally...
Prompt for name of new thing from a popup with combolist
Ask for a few more items of essential info
Make checks against existing data (searching clones with ADO)
Create new record (Docmd.GotoRecord , , acNewRec)
Populate fields on record with information collected above.
Leave user to fill in extra info on form and subforms.
Oh and the user gets an undo button (that sometimes works, sometimes doesn't).
Docmd.GotoRecord , , acNewRec doesn't seem like a very good method anymore now I have an SQL back end.
For instance a field that is =mnManufacturer + ' ' + eqMake doesn't display this sum any more as this new record is in progress.
Do I need an update or save command at some stage?
This is one area where I never felt very in control of where my data actually was. Am even less sure now I am using sql server!
Maybe use ADO to create new records.
Or should I use SQL commands directly to INSERT data.
Or something else.
I am aware that there is a issue with the time that autonumbers and prmary keys are created and when they are available for use by code.
The undo feature is nice but not essential (would be nice to have a bit more consistancy).
I want to head down the right path now rather than revisit it all later.
So what method do you lot recommend?
Simon
"Do I need an update or save command at some stage?"
You could:
1. Update the value in the After_Update events of the controls for Mfg and Make.
2. Use code to insert the value after you have collected Mfg and Make
3. Save the record as soon as you have enough information to satify required fields validation
4. Force it with Me.Calculate
To follow on with rocki ...
Is the form and it's controls bound to a recordset/view?
This will greatly affect how you add new records, commit changes to the actual database, undo uncoimmitted changes etc.
Steve
You could:
1. Update the value in the After_Update events of the controls for Mfg and Make.
2. Use code to insert the value after you have collected Mfg and Make
3. Save the record as soon as you have enough information to satify required fields validation
4. Force it with Me.Calculate
To follow on with rocki ...
Is the form and it's controls bound to a recordset/view?
This will greatly affect how you add new records, commit changes to the actual database, undo uncoimmitted changes etc.
Steve
ASKER
Sorry thought that was a given, yes the forms are bound to a view, do I want to stay that way.
In which case I assume the DoCmd route is the most obvious (is it?).
But as I said as it stands code methods that worked for a mbd do not behave very well in adp.
BTW I do not have Mfg or Make controls on the form just a calculated control to give the concatenation of both.
That isn't the only bit that isn't behaving, it is generally clunky and requires closing and reopening the form to see the new record properly.
The worst behaving are where the form is bound to a view that pulls from several tables (up to six).
Yes I have set unique table.
I know you might like to see some specific code but the answer I'm looking for is what is the best generic approach to new record creation and the issues therein for my particular style of application. Remember too I am coming from a rounded mdb application so my form design and the experience I present the user with is now set.
The reasons for sql move are stability and speed. So it would be interesting to know the approach an SQL/Access developer would have started with in the first place when it comes to forms.
cheers
Simon
In which case I assume the DoCmd route is the most obvious (is it?).
But as I said as it stands code methods that worked for a mbd do not behave very well in adp.
BTW I do not have Mfg or Make controls on the form just a calculated control to give the concatenation of both.
That isn't the only bit that isn't behaving, it is generally clunky and requires closing and reopening the form to see the new record properly.
The worst behaving are where the form is bound to a view that pulls from several tables (up to six).
Yes I have set unique table.
I know you might like to see some specific code but the answer I'm looking for is what is the best generic approach to new record creation and the issues therein for my particular style of application. Remember too I am coming from a rounded mdb application so my form design and the experience I present the user with is now set.
The reasons for sql move are stability and speed. So it would be interesting to know the approach an SQL/Access developer would have started with in the first place when it comes to forms.
cheers
Simon
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I already use the ADO recordset method extensively when programatically changing values in the tables.
(this was with a view to upsizing several years ago)
The only time I use the docmd.gotorecord, , acNew is when creating a new record on one of my six main forms.
Thinking back this stems from the first implementation of the address book in access 5+ years ago.
What do you think, go with ADO recordsets for all data manipulation and creation?
Simon
(this was with a view to upsizing several years ago)
The only time I use the docmd.gotorecord, , acNew is when creating a new record on one of my six main forms.
Thinking back this stems from the first implementation of the address book in access 5+ years ago.
What do you think, go with ADO recordsets for all data manipulation and creation?
Simon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried to quickly knock up an ADO recordset based version but this still exhibits the issues I had with Docmd plus I would need to implement a technique to get the PK (an autonumber) so that I could then programatically navigate to this new record. It seems to be a problem with my forms based on complex views too. After adding new records the whole thing slows down and navigation becomes corrupt (records themselves are fine) until I close and re-open the form.
I'd rather use a technique that makes use of my bound form.
I shall try the Me.Recordset.AddNew method tomorrow.
Since I am in an ADP does this mean that this is implemented with ADO too?
It also strikes me that I should show the navigation toolbar again just to see what happens when I use that to create a new record (since this is what the Docmd actions should be running)
cheers
Simon
I'd rather use a technique that makes use of my bound form.
I shall try the Me.Recordset.AddNew method tomorrow.
Since I am in an ADP does this mean that this is implemented with ADO too?
It also strikes me that I should show the navigation toolbar again just to see what happens when I use that to create a new record (since this is what the Docmd actions should be running)
cheers
Simon
ASKER
OK my problem has changed as I analysed it.
I now know what question to ask!
Should I close this question and ask a new one or just keep plugging on??
I now know what question to ask!
Should I close this question and ask a new one or just keep plugging on??
entirely up to u - whatever u feel is best
ASKER
what do I do? delete this one?
if u want to close this question then u have to make a request to close this question and refund the points
go to Community Support section
the alternative is to ask the question again here, and create a new question, say a 20ptr question, asking the new question and with a link to this question
go to Community Support section
the alternative is to ask the question again here, and create a new question, say a 20ptr question, asking the new question and with a link to this question
ASKER
I have split the points to close the question.
My real problem and the solution I found is at
https://www.experts-exchange.com/questions/21102705/ADP-SQL-2000-only-Problem-adding-records-to-bound-forms.html
It's all down to getting the forms recordset into sync with new records in the sql when the form is bound to anything more complex than a single table.
I think I'll be in a position to write a article on how to get a 2000 mdb into a 2000 sql/adp after all this.
My real problem and the solution I found is at
https://www.experts-exchange.com/questions/21102705/ADP-SQL-2000-only-Problem-adding-records-to-bound-forms.html
It's all down to getting the forms recordset into sync with new records in the sql when the form is bound to anything more complex than a single table.
I think I'll be in a position to write a article on how to get a 2000 mdb into a 2000 sql/adp after all this.
with bounded forms, u can add buttons to perform finds etc