Unbound controls to bound

[Voltron]

I am under directions from my bosses to use unbound controls and then have the input go to the appropriate fields in the appropriate tables upon the clicking of a submit button.

I am not sure how to go about writing code for this. Could anyone give me a hint as to where to start?

I had the following idea, but I am not sure if this would work or how to finish it...

Me.txtUserID.ControlSource = USE SQL TO GET SPECIFIC FIELD IN SPECIFIC TABLE

...does this make sense?

 

[pbaldy]

You would open a recordset on the data, then

Me.ControlName = Recordset!FieldName

 

[The_Doc_Man]

I think I understand what they are asking, though it betrays a total lack of trust of their employees, I think.

It is easy to make unbound forms that perform an update on demand.

First, create the unbound controls where you enter data.

Next, in each contributing control's {control}_LostFocus event routine, do your auditing, editing, sanity checking, or whatever you call it at your site - for that one field.

Then, build a button with a click event to do anything like SAVE or UNDO or darned near anything else except QUIT. You are going to rewrite that anyway.

The code under the SAVE_CLICK button should

1. Open a recordset to the "real" table
2. recordsetvar.Addnew
3. One at a time, copy the contents of the unbound controls to the fields of the recordset, using CInt, CLng, CDbl, CDate as appropriate, since ALL text boxes are ... wait for it... TEXT format. Though if you had an option group you could get back a number for it, and a checkbox will be zero for FALSE and something else for TRUE.
4. recordsetvar.Update
5. recordsetvar.Close

If you need to do a DONOTSAVE button, just go through and erase the controls that contribute to your operation. As a matter of fact, make that a subroutine in the form's class module, 'cause your SAVE action probably wants to erase those same fields when the save is done.

That's at least the overview. There might be more elegant methods, but if you are stuck with the problem as your boss stated, that's how I might do it.

 

[Voltron]

You would open a recordset on the data, then

Me.ControlName = Recordset!FieldName

Yeah, but...how DO you open a recordset on the data. I have never done this.

This is what I have and it gives me the your blah blah does not match the specified blah blah procedure yada yada yada.

Option Compare Database
Private Sub btnSubmit_Click()

Dim rst As ADODB.Recordset
Set rst = SECOND_ATTEMPT_CAA_Monitoring_Lance.OpenRecordSet("Tbl_Analyst_Status", dbOpenDynaset)
Me.txtPopulate = rst!Anaylst_Status_ID

End Sub

'1 button btnSubmit
'1 textbox txtPopulate

 

[pbaldy]

I guess you can't search on recordset as I recommended in the other thread?

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT Field1, Field2 FROM TableName WHERE Field3 = 1"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  set rs = Nothing
  set db = Nothing

 

[Voltron]

I guess you can't search on recordset as I recommended in the other thread?
quote]

I am but I figured I could as and see if that worked. I have been searching through a ton of stuff and so far I am finding that what other people have used I am not able to use whether it be the version (2003) or user error.

 

[gemma-the-husky]

dcrake might disagree (as he says he uses a lot of unbound forms) but I feel it is far far harder to write a coherent application with unbound forms rather than bound forms. I should think I have at least 90% bound forms, and unbound only in certain cases where I feel the app
needs it.

You will have to programme differently to manage things as (logic) errors (eg checking for existing records) will have to be done in a different way. You can also lose some of the rigour that bound forms supply - eg if you have a text field 8 characters long, a bound form will manage that automatically - not so easy at all with an unbound form.

Why do your bosses feel this way? do they mean they want staff to pre-batch and control input - because you can still do this with bound forms- thats just planning the application differently. Are they experts, or has someone told them this is a good idea.

I appreciate It's a hard problem for you to solve, but why should it matter to them how data is added to a database - the real control they need is

a) identify the data before the processing started
b) identify what new data was added
c) confirm that the data still reconciles


Good luck

 

[Voltron]

Dim db As DAO.Database

This results in an error. Compile Error: User defined typed not defined.

Apparently Database does not exist like Recordset does. Do I need to write specific code declaring it as a type of variable?

 

[gemma-the-husky]

Dim db As DAO.Database

if that errors, it sounds like the reference isnt checked - the DAO library is DataAccessObjects (version 3.6 I think)
probably listed as MS DAO 3.6

open a code module and check tools/references

---------
out of interest, note that the DAO prefix is not always necessary, depending on the references (eg database exists in both DAO, and ADO)
the default is taken as whichever is named first in the references list.

If you only use one (and access standard is DAO), just remove the other reference, and then you dont need to qualify the library reference.

 

[Voltron]

dcrake might disagree (as he says he uses a lot of unbound forms) but I feel it is far far harder to write a coherent application with unbound forms rather than bound forms. I should think I have at least 90% bound forms, and unbound only in certain cases where I feel the app
needs it.

You will have to programme differently to manage things as (logic) errors (eg checking for existing records) will have to be done in a different way. You can also lose some of the rigour that bound forms supply - eg if you have a text field 8 characters long, a bound form will manage that automatically - not so easy at all with an unbound form.

Why do your bosses feel this way? do they mean they want staff to pre-batch and control input - because you can still do this with bound forms- thats just planning the application differently. Are they experts, or has someone told them this is a good idea.

I appreciate It's a hard problem for you to solve, but why should it matter to them how data is added to a database - the real control they need is

a) identify the data before the processing started
b) identify what new data was added
c) confirm that the data still reconciles


Good luck

I agree with everything you have just said, which I think we agreed on before (lol). My bosses just want it unbound. Aboslutely everything should be unbound now. I am an intern and was extremely proud of what I gave them today, but the first things they said after 2 minutes of use was "we are going to scrap this whole things and have you start over with a blank database."

Why they feel this way I have no idea. They have been pissy about the user being able to directly type into the table from the form, which shouldn't matter because that is what is supposed to happen because of all of the other error checking I have. The only thing I needed to add was error checking so that the form could not be closed or that the data would be erased if the form was incomplete and the user hit the close button.

I completely disagree with having something this simple (which I would post but they TOOK IT AWAY FROM ME ENTIRELY and before I could save it to a flash drive to keep for my own enjoyment because I am DAMN proud of it).

I am honestly to the point of saying that I no longer want to be a part of the project and simply go back to the accounting part of the internship that I was hired for.

 

[gemma-the-husky]

well i suppose it depends on your position

if you arent bothered, do it - if you are, maybe you CAN just walk away? I can't think of any justified reason for them to insist on this. But think about it carefully.

 

[Voltron]

well i suppose it depends on your position

if you arent bothered, do it - if you are, maybe you CAN just walk away? I can't think of any justified reason for them to insist on this. But think about it carefully.

I am taking my time with my decision, but it is really difficult. They expect me to learn how to use unbound controls, send data (input) from an unbound control to a field in a table and learn all of the new ways to do error checking by the end of today (4 hours from right now actually). This is simply unreasonable as far as I am concerned.

If they simply would have told me that no matter what the controls had to be unbound I would have been able to learn how to do that by now and everything else they asked of me, but I have essentially wasted 3.5 weeks of my time on this stuff when I could have been getting experience in the areas that I need the experience in.

 

[David R]

Are they ACTUALLY after error-checking, maybe? That will be a lot easier to check for with a bound form, rather than recoding everything.

::edit:: Sorry, I just read the rest of the comments, including your description of the meeting. I would tell them that this IS the safe way to do it (unbound forms tend to lead to lost data - what if the computer locks up, crashes, power goes out?), and if they want to do it their way, they should find someone else to do it.

An internship is not license to abuse somebody.

 

[boblarson]

It's tough when you run into idiots who think they know what they're doing. Bound forms can be just as "safe" as unbound forms, if you know what you're doing. Problem is, I doubt they could punch their way out of a paper bag, based on your description.

You gotta love those uneducated decision makers who think they know database development. Perhaps they do to some extent, but they obviously are mental midgets when it comes to Access.

Good luck with everything and I know that if it were me I would be telling them, in no uncertain terms, where to stick it.