Code to make controls bounded upon submit btn click

[Voltron]

I have a database and have to create forms for data entry. However, I have been informed that having the controls bound to the fields in the tables is out of the question.

I was just wondering how I would go about getting the information to properly submit and be transferred to the appropriate field in the appropriate table.

Would something like...

me.txtUserID.Control Source = USE SQL TO RUN QUERY HERE

...work or would I have to do something else.

I woulde use this code in the submit button right before the close of the record.

I know this sounds weird and a little redundant and I have explained this to my superiors, but this is what they want and they have not budged. I was thinking of having a continuous form and then having the user hit a button to get ready to submit and then the submit button replaces the READY button for being visible.

Would this code work to bound the information upon submission? Or is there something else I should do?

 

[DCrake]

The main difference with Bound and unbound controls is that you have to instigate the record appends/updates/deletes manually. Any validation is generally done on the AfterUpdate event of a control and buttons are used to run code that will perform the necessary action(s).

You have to tell Access which table needs updating which field in the table needs updating and which unbound control contains the data that you want to update with.

David

 

[Voltron]

The main difference with Bound and unbound controls is that you have to instigate the record appends/updates/deletes manually. Any validation is generally done on the AfterUpdate event of a control and buttons are used to run code that will perform the necessary action(s).

You have to tell Access which table needs updating which field in the table needs updating and which unbound control contains the data that you want to update with.

David

So...what I need to do is put all of the necessary code in an AfterUpdate even for, say, a textbox for User Id's (txtUserID).

What I would do would be to use specific code to tell the DB which field in which table the information is connected to along with any data validation checks I want to run.

Is this correct? I just want to make sure I am on the right path.

 

[CEH]

I'm rather curious about the first part of your statement....
"I have a database and have to create forms for data entry. However, I have been informed that having the controls bound to the fields in the tables is out of the question."
How is your information being saved into a table if not bound to the table? Going to save it all via coding?
Take a basic table... say tblPerson. Three fields. PersonID (autoNumber), txtFirstname (text), and txtLastname (text). Build a query from the table with all three and build a form from the query. Your three controls ARE bound to the table.
Once you enter a first name and last name and move to a new record or close the form your record is saved to your table.
Your buttons and validation code or whatever your superiors want to call it is the only place you will need the coding.

 

[Voltron]

What would the code be like to get the input to the correct table?

Would I need to use something like...

me.txtUserID.ControlSource = ...

or something else?

Is SQL involved at all for telling the form where to send the data?

 

[Voltron]

I'm rather curious about the first part of your statement....
"I have a database and have to create forms for data entry. However, I have been informed that having the controls bound to the fields in the tables is out of the question."
How is your information being saved into a table if not bound to the table? Going to save it all via coding?
Take a basic table... say tblPerson. Three fields. PersonID (autoNumber), txtFirstname (text), and txtLastname (text). Build a query from the table with all three and build a form from the query. Your three controls ARE bound to the table.
Once you enter a first name and last name and move to a new record or close the form your record is saved to your table.
Your buttons and validation code or whatever your superiors want to call it is the only place you will need the coding.

I understand your confusion. I don't know why they want it this way either. I would think that the way it is is fine, but they want to do it this way.

I have learned that apparently you can make the controls unbound by going to the properties in design view and changing the Control Source to an empty line. However, there is a lot of coding to do after this to get the unbound controls to be bound again.

My bosses want it done this way because they want to do the error checking all in one fell swoop as opposed to how I had it originally, which was record by record when the user was entering in the data and when they made a mistake they would be notified via a messagebox. They really disliked it and chastised me for having so many error messages.

 

[missinglinq]

There is no "one fell swoop" where you can do your error checking on all the records rather than checking one record at a time. To begin with, unbound forms only allow for the entering of a single record at a time; you cannot enter 10 records and then save all ten; why would you want to? What if you have ten records and there are different mistakes in five of them. You still have to check the records, one at a time, for mistakes, correcting them, one at a time, as they are found.

Using an unbound form for data entry generally means that the person has no idea what they're doing about vis a vis Access. It also means that development time and hence development cost, for experienced developers, will increase by a factor of ten. For inexperienced developers, the increased development time/cost will be even larger.

If "they" are so concerned about the messageboxes popping up all the time, which is the long accepted way of dealing with incorrect/inappropiate data, they need to take the time to learn what is and isn't appropriate, rather than bashing the developer.

 

[Voltron]

There is no "one fell swoop" where you can do your error checking on all the records rather than checking one record at a time. To begin with, unbound forms only allow for the entering of a single record at a time; you cannot enter 10 records and then save all ten; why would you want to? What if you have ten records and there are different mistakes in five of them. You still have to check the records, one at a time, for mistakes, correcting them, one at a time, as they are found.

Using an unbound form for data entry generally means that the person has no idea what they're doing about vis a vis Access. It also means that development time and hence development cost, for experienced developers, will increase by a factor of ten. For inexperienced developers, the increased development time/cost will be even larger.

If "they" are so concerned about the messageboxes popping up all the time, which is the long accepted way of dealing with incorrect/inappropiate data, they need to take the time to learn what is and isn't appropriate, rather than bashing the developer.

I absolutely agree with everything you said. I was just trying one last time to see if I was missing something, but I guess I wasn't.

I will simply let them try to figure it out if they want to. I was not sure at all about the multiple errors in multiple records like you talked about, but now I see exactly what will happen if the forms are unbound to start with...a lot of wasted time and effort all to get back to what I had done originally, one record at a time with error checking for the record.

Thank you all for your advice and guidance. I truly appreciate it.

 

[DCrake]

Missingling:
Your comments on using unbound forms and the expertise of the developer is somewhat disconcerting. I have been developing for over 25years and have generally used unbound forms for that reason. forcing the user to commit a save record. I usually have a validation check on all fields when the uses selects Save. Ok I agree there is more work involved but I have more control over what happens. For example if I have a button on a form that says "Add New Record" I usually follow that up with an MsgBox "Are You sure?" just in case the user clicked the button by mistake.

With bound forms you have to worry about undoing actions after the fact. This can lead to gaps in autonumbers if two people are in the process of adding a new record and one of them cancels the addition.

David

 

[gemma-the-husky]

but to go back to the original point

why do you think you CANNOT use a bound form. What are you trying to do?

Bound forms make sense for some things, and unbound forms for others. Bound forms are in general far easier to use - but it is quite strange in general terms that you think you HAVE to use an unbound form.

 

[Voltron]

but to go back to the original point

why do you think you CANNOT use a bound form. What are you trying to do?

Bound forms make sense for some things, and unbound forms for others. Bound forms are in general far easier to use - but it is quite strange in general terms that you think you HAVE to use an unbound form.

I think this one was directed at me. If not then please just ignore my rambling. lol

I was told that the Unbound form was the way I was supposed to do this project. I started with and still have a bound form that I have been working my way through. I have been learning a tremendous amount about the greater complexities and power that Access allows the developer, thanks in large part to all of you on this site.

I prefer bound forms, especially at this point in time. The main reason that I was told to use a bound form was so that the users could enter in information for, say, 10 records and then submit the information, with most of the error-checking occuring immediately before submission.

However, I don't see the logic in this because the same thing could be accomplished, it seems, with simply using a bound form that makes use of the continuous form property and then submitting everything from the continuous form.

Also, since the leaders of the project want the user to enter one record at a time the error checking would have to be done record by record anyway. It seems like it would be easier for the user to see what their mistakes are for the record that they are working on before moving on to the next record. Information may be lost, they may be called away from their desk and have to stop before correcting things, leading to a lost record because it is never entered.

I hope that answers the question and my feelings on the subject.

I am willing to do an unbound form, but I just need to know what I am supposed to do to get the information to submit properly.