unbound text box based on sql in vba?

[vicsaccess]

ok, time for me to learn a new trick. i have table that is great for storage, all the values are numeric numbers representing descriptions in other tables, basic good table. my problem is that to view the data in a form i have to use too many queries to make the data legible for the users and then they can't edit the data. i know i can use recordsource to bring in the table numeric data to unbound text boxes that i could then use update queries to change any data that they may need but how do i get the numeric data legible to the persons using the page? can i put a query into the vba? here's the code for the form that i'm trying-

Private Sub Form_Load()
    Dim sql As String
    Dim locsql As String
    
    Dim db As Database
    Dim rs As DAO.Recordset
    
    sql = "select * from workquet ;"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sql)
    
    Me.txtloc.Value = rs![LocID]
    Me.txtdept.Value = rs![DeptID]
    Me.txtsystem.Value = rs![SystemID]
    Me.txtasset.Value = rs![AssetID]
    Me.txtcomp.Value = rs![ComponentID]
    Me.txtpart.Value = rs![PartID]
    
    Set rs = Nothing
    Set db = Nothing

and here's the SQL that i would use in a single query to get the value for the first txtloc

SELECT [3LocationT].Location
FROM 3LocationT INNER JOIN WorkQueT ON [3LocationT].LocID = WorkQueT.LocID;

i'm sure this is done, its time to learn it

[June7]

Why are you using unbound textboxes and code? Is this an unbound form - why?

What do you mean by 'too many queries'? If these are simply lookup tables and you include them in the form RecordSource - do not use INNER JOIN - the dataset should be editable and the related info can be displayed in bound textboxes. However, do not allow edits of the lookup tables data, set the textboxes as Locked Yes.

[vicsaccess]

Thanks June, thats working but was hoping to advance my knowledge a little if it was possible to do it with unbound text boxes. sometimes i like the unbound so i can keep the records added controlled by means of an update button rather than a tab button that gives me blank records

[June7]

Just as one form RecordSource should be able to include all related lookup data, so should one recordset. Then set values of textboxes from the recordset. Exactly what issue are you having with this approach?

[vicsaccess]

only one issue, i have the form set up as a sorting point for requested work, if the work is legitimate then the supervisor uses a list box to select "proceed", as they use the next record button any record marked "proceed" gets inserted to the workque table. at the same time any record marked "proceed" or "delete", a Boolean field gets marked "true". the advantage of the form being based on the query recordset is that it is loaded and sorted upon the Boolean field being "false". that way if a record has already been reviewed its an easy way that takes up a small amount of space to tell reviewed records. with the 8 queries it takes to get this legible for the supervisor the records are no longer editable so i can't get the Boolean field to change to true and my forms sort does no good. in all reality the Boolean field "done" is the only one that needs to be editable, what if i use the above code for that field only? i give it a try later, if any ideas please point me in a direction.

[June7]

I do not understand how this involves 8 queries.

If you want to provide db for analysis and testing, follow instructions at bottom of my post.

[vicsaccess]

Maintenance_Complete.zip

thanks June, i believe i have accomplished what i need. i like the look of these forms. the form that i was questioning was the review form needing to update the "done" value to true which in turn pulled it out of the forms query. i ended up using an update query to add the true value to the table which seems to be working. since i'm still a learner please feel free to take a look and make suggestions as i'm ultimately after an efficient running database.

[June7]

HealthT, WorkT, PriorityT need primary key field designated. Then the form's dataset is editable. However, the info from the lookup tables should not be edited through this form. Set those textboxes as Locked Yes. Change the RecordSource to include the [done] field. Then cboreview can be bound to field. Which value is True - "Proceed"? Change the combobox properties:

ControlSource: done
RowSource: -1;Proceed;0;Delete
ColumnCount: 2
ColumnWidths: 0";1"

You appear to be emulating a Split form - have you explored this object type yet?

[vicsaccess]

thanks june, i'll make those changes. yes this will end up being a split, i'm just doing it all together for ease. any suggestions or things that you would do different on the code?

[June7]

I asked if you explored Split form, not split database.

However, glad you are aware of split database concept.

What code? With my suggested changes, no code is needed just for saving the combobox selection. I don't understand the need to duplicate data into WorkQueT.

[vicsaccess]

i tried some split forms (sorry i read and typed faster than i comprehended) and just didn't like the way they worked with what i am trying to accomplish in the end. the request table is only for request entry. there might be hundreds of these a month and this is all the data needed. once the request is approved then the add ons will start in the workque such as job plans, parts list. the request table can then be purged periodically. also the workque will have re-accuring task such as grease a wheel monthly that will just keep rotating with new due dates each time the work is completed.

[June7]

I agree, performance of Split form is less than great. I have used it only once.

I would not have two tables.

Do you care about history of work? Do you need to know all the dates a wheel was ever greased? How many times it has been greased?

[vicsaccess]

history, absolutely. but only for the work that makes it to the workque table. the request table is only a temp holding spot until the supervisor can review it and decide to move forward or denie. due to the aspect of the history of everything i'm trying to be very careful with my data types and values so that the workque table can be as compact as possible. debated on actually moving the workque data to an achieve table once the maintenance task was complete but with reaccuring work it makes it simple to leave it there and just renew the due date each time its completed. still learning but have a good time at it.

[June7]

I still think 2 tables are not needed. Deleting records should be a rare event - once it is gone there is no retrieving no matter how much you regret deleting (unless you keep backup files). There is no need to ever delete (purge) a request record. Every request should have a resolution documented, even if it is 'denied'. Just change the value in a status field and apply filter criteria. You want an efficient db, IMO the 2 table approach and 'moving' records is not efficient. But, do what works for you.

[vicsaccess]

agreed on deleting records, another aspect of this is that the individuals i doing this for at work actually want the records deleted so this is my way of making everyone happy, they see that they are deleting records out of the request table but what they don't know is that they are just filling in the field "review" with the word delete so i query those records out. by purging the data i mean to go into the request table a few times a year and back it up to another location so that the deleted data does not bog down the performance but is still a good archive of information.