Populating unbound controls on a single unbound form using VBA

[dkc123]

Dim db As Database
Dim rs As DAO.Recordset

Dim sqltbl As String

If IsNull(Me.OpenArgs) = False Then
sqltbl = ("Select TestName, TestValue FROM NonProfileTests WHERE TestOrderID ='" & Me.OpenArgs & "")

Set db = CurrentDb
Set rs = db.OpenRecordset(sqltbl)
Set Me.Recordset = rs
rs.Close
Set db = Nothing
Set rs = Nothing
End If

 

[LPurvis]

The mechanism you're attempting isn't unbound.
You're binding the form to a recordset, thereby making it bound. (It's simply being bound late.)
You would have control sources set up for the textboxes as normal (based on the field names in the table).

If you want a truly unbound form, then you don't assign the recordset to the form as you've done (Set Me.Recordset = rs), you enter the values of the row in the controls in code - but you're then responsible for record navigation and updating.

There should really be a specific reason to be doing the assignment as you currently are. (Unless it's just to learn - in which case, that's fantastic.

 

[dkc123]

Thanks I was'nt aware about the the form being bound even though I could see the Query in the form recordsource in the properties table..
I am a doctor and not a software engineer, I have learned everything from the net and tutorials..
Will give a little backround first, this is from an existing application for my lab which is already working ( quite fine in fact) but I wish to improve it.

The main tables are PatientReg (PatientReg)( This keeps data of the patients being tested ..RegistrationID, NameID, SurnameID,,age etc..); table-TestName ( this stores Names of all the tests -TestNameID, TestName etc..); Table - TestOrderIDtbl ( This stores all tests booked with a unique TestOrderID, RegistrationID, TestOrderDate etc...) Then Table- NonProfileTests ( This stores the TestNames from Table TestName and their values for a particular TestOrderID.

Since there a very long list of tests which can be performed in the lab I had to keep this structure, Otherwise I could have kept All the tests on the X(Columns) axis and just filled up patient names on the Y(Records)..So for each TestOrder their may be 2 or three or 10 or hundreds of tests which may all be different at all times..
So in every TestOrder we book entries are made in the NonProfileTests at the time of booking (TestNameID's), the lab people just have to fill in the results observed into corresponding TestValue.

Currently I am using
1) Bound Forms to update values ( This is user unfriendly as with current structure its possible only as a continuous form, eg. if I wish to change value then I have to look for it roll up and down )
2) using add new to add and update some values of profiles..( Profile means that one test is entered but actualy 5 or more tests need to be done eg liver profile may contain 7 individual tests..Drawback here is that I cannot see the values of same parameters if they have been filledup earlier..as some profiles may have common parameters..)

What I want to do now is make an unbound form, ( I think it would load faster, I have more than 10000 pts data now) when it loads it should load the values of most of the commonly done tests ( About 10~15 or so) if they are already there If some changes are required we should be able to make them then we can save them using the edit method,

Now I am getting stuck at this step of loading values into the controls..I have changed earlier forms, all the profiles are lookedup using a query and NonProfileTests populated using query, but I am not able to see the values of Individual tests in a single form...( It is possible in a continuous form buts its not convenient for the end user..)
I've tried to write in detail but I feel it may confuse you...Thanks a lot in advance fr trying to understand me..

 

[dkc123]

I would try to put things simpler, If I have a Table TblTestname with fields TestName and TestValue like below
AutoID TestName TestValue
1. 101 25
2. 102 35
3. 103 1265
4. 104 45
5. 105 65
6.
7.

Then I need that the form with textbox name 101 to be populated with 25, 102 to be populated with 35, 103 to be populated with 1265 and so forth…..In reality I would be using a query to extract data from a single table.

 

[JHB]

Use a recordset and a loop.
Be aware of naming the control with a number in front is not preferable, put a letter in the front of the name, like "C", then the name would be "C101".

You refers to the control on the form like so:, (... = Recordset name)

Me("C" & ...![TestName])=....![TestValue]

 

[dkc123]

Use a recordset and a loop.
Be aware of naming the control with a number in front is not preferable, put a letter in the front of the name, like "C", then the name would be "C101".

You refers to the control on the form like so:, (... = Recordset name)

Me("C" & ...![TestName])=....![TestValue]

Thanks fr replying , I'll try and get back, I'm sure this will work..

 

[dkc123]

Opening a bound form to a large record is inefficient. A better technique is to use a search box or combo to locate ........

Thanks a lot for all the help! I feel access has only improved the way I work, learning new things is just a part of my profession!! I'll definitely use the code for the combo, till yet I'd been using the combobox wizrd for the same purpose..Thanks

 

[dkc123]

Here is the solution that is working fine for me, just sharing so that if anyone else has a problem may find helpful...
Private Sub Form_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim cnt As Control
Dim sqltbl As String

If IsNull(Me.OpenArgs) = False Then
sqltbl = ("Select * FROM NonProfileTests WHERE TestOrderID = " & Me.OpenArgs & "")


For Each cnt In Me.Controls
Set rs = CurrentDb.OpenRecordset(sqltbl)

rs.MoveFirst
Do While Not rs.EOF
If cnt.Name = ("txt" & rs!TestName) Then
cnt = rs!TestValue
cnt.Visible = True
End If
rs.MoveNext
Loop
rs.Close

Next cnt

Set db = Nothing
Set rs = Nothing
End If

End Sub

Thanks for your help which led to the solution..