By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,982 Members | 1,738 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,982 IT Pros & Developers. It's quick & easy.

Datasheets

P: 37
Hi all,

I have been pondering this for about 2 hours now, I am changing Bound text boxes to unbound as more than one user is using the form. Trying to create a datasheet without success :-(

This is probably on the forum somewhere and I have probably taken the completely wrong approach but here is my attempt!

Expand|Select|Wrap|Line Numbers
  1. Dim DescrSQL As String
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Form.datasheet
  5. DescrSQL = "SELECT [ID], [title], [FName], [LName] FROM paper where fk_link = " & [Forms]![MainMenu]![Child0].[Form]![subform_full_details].[Form]![PSID] & ";"
  6. Set db = CurrentDb()
  7. Set rs = db.OpenRecordset(DescrSQL)
  8. rs.MoveLast
  9. rs.MoveFirst
  10.  
  11. Do Until rs.EOF
  12.     ID.Value = rs![ID]
  13.     Text1.Value = rs![Title] & " " & rs![FName] & " " & rs![LName]
  14.     rs.MoveNext
  15. Loop
  16.  
  17. rs.Close
  18.  
Thanks in advance

Matt
May 9 '07 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,471
Matt,

You posted this question in the Access Articles section rather than the Access Forum section. I've moved it to the correct place.

MODERATOR.
May 9 '07 #2

NeoPa
Expert Mod 15k+
P: 31,471
Having now read your question Matt, I'm afraid you will need to rephrase it as it is far too much of a "I have a general unspecified problem & I want someone to redo it all for me." type to get much of an answer.

(POSTING GUIDELINES: Please read carefully before posting to a forum) may help you get a better understanding of how to post a question. Particularly, you need a specific question rather than a request for someone to redesign your project for you.
May 9 '07 #3

P: 37
Giggle!

I wish someone could do it for me! But there again i ain't gonna learn now am I!

Would I at least be along the right lines or have I done it completely wrong!
May 9 '07 #4

P: 37
I done it again!

Ill start from the beginning, trying to create this form, started off using binding now moving away from that, but I am a COMPLETE beginner when it comes to writing this - been looking all over the place.

All I want to do is learn how to create a basic list and output it in the datasheet format - the form has already been setup to do this.

I want to put the values into 2 text boxes - because this is how you do it with binding. Is the unbound way done the same or do I have to do it differently!

Does that make sence?
May 9 '07 #5

NeoPa
Expert Mod 15k+
P: 31,471
I done it again!

Ill start from the beginning, trying to create this form, started off using binding now moving away from that, but I am a COMPLETE beginner when it comes to writing this - been looking all over the place.

All I want to do is learn how to create a basic list and output it in the datasheet format - the form has already been setup to do this.

I want to put the values into 2 text boxes - because this is how you do it with binding. Is the unbound way done the same or do I have to do it differently!

Does that make sence?
Not a lot I'm afraid Matt.
Start off by explaining why the list must be made unbound. I'm not connecting multi-user with unbound here.
Next, why does a static list (you explained you just wanted to show the list) need to worry about multiple users anyway?

In general, steer well clear of populating unbound datasheet (especially but others too) forms.
May 9 '07 #6

P: 37
Not a lot I'm afraid Matt.
Start off by explaining why the list must be made unbound. I'm not connecting multi-user with unbound here.
Next, why does a static list (you explained you just wanted to show the list) need to worry about multiple users anyway?

In general, steer well clear of populating unbound datasheet (especially but others too) forms.

I was under the impression that making forms unbound gives you
a) more control
b) made the form load up quicker
c) makes it quicker for updates to be made, as in when an update is made, the table is not locked by the user.

I can see why you think to stay clear of populating unbound datasheets - its not easy to do! At the end of the say, i want it to say the primary key and the name of the person in a list, when the user clicks on that record, all the other details are loaded up in another subform. No locked reocrds, no errors associated with those locked records.
May 9 '07 #7

Expert 100+
P: 344
I was under the impression that making forms unbound gives you
a) more control
b) made the form load up quicker
c) makes it quicker for updates to be made, as in when an update is made, the table is not locked by the user.

I can see why you think to stay clear of populating unbound datasheets - its not easy to do! At the end of the say, i want it to say the primary key and the name of the person in a list, when the user clicks on that record, all the other details are loaded up in another subform. No locked reocrds, no errors associated with those locked records.
I think I know what you mean. You want an unbound form that can display the data one record at a time, allow the user to edit, then save the data. If that is what you want, this is how I do it.

Create your form with bound data so that each control on the form has the same name as the field in the recordset. Then set the control source of all the control to blank, i.e. unbind the form, and remove the recordsource for the form.

In the form footer, have a combo box that has the record set you want. Call this, say Select Record.

When the users picks a record from the combobox, use the key from that record to open a recordset of just 1 row, with all the fieldnames you want to display.

You can now either say
name=rs!name
title=rs!title
etc
or be clever and write something that loops through all the fields in the recordset, something like
me.rs(fld.name)=rs(fld.value) 'This is not correct, but something like it.

Then close the recordset. Your user is now looking at the unbound data and can make changes.

you then have to write a save button and put code behind it to
Open the recordset of 1 record
rs.edit
update all the fields that have changed
rs.update
close the recordset

You would also have to do all your validation before saving and take account of someone else changing the data from when you grabbed it to when you put it back.

It can be done and yes, it makes the system run a lot faster and stops locking errors. I wrote a system like this for Nat West Bank in Access 2. It took me about 1 week to get the generic open, edit,save,delete,add new stuff working, and nearly 2 months to get reliable generic error handling to handle all the changed records, locks etc.
May 9 '07 #8

P: 37
hmm, I think i will just bind the datasheets and have them read-only and unbind the rest.

Thank you for your help tho!
May 10 '07 #9

NeoPa
Expert Mod 15k+
P: 31,471
I'm glad you've got a way forward at least.
Even with answers to my questions I suspect I would have shown you in that direction anyway.
May 10 '07 #10

P: 37
It would of been good to know how, I may have to purchase a few books and find out!
May 11 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.