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]

 

[Pat Hartman]

Opening a bound form to a large recordset is inefficient. A better technique is to use a search box or combo to locate records. The simplest way to implement this is to change the RecordSource of the form to a query with criteria.

Select ...
From ....
Where SomeField = Forms!yourform!yourcriteriafield;

Then, in the AfterUpdate event of the search combo, requery the form. This will bring up only the record or records that match the criteria.

Me.Requery

Since you are a doctor, you probably have better things to do than to learn VBA which is what you will need to do to work with unbound forms. The method I suggested requires a single line of VBA. You could use a macro but in my opinion, that would be more complex.
If you have a situation where you have multiple search options, the query criteria becomes a little more complex and instead of using the AfterUpdate event of the combo, I would add a button so the requery only happens when the user has entered all the criteria.

Select ..
From ...
Where (fldA = Forms!yourform!fldA OR Forms!yourform!fldA Is Null) AND (fldB = Forms!yourform!fldB OR Forms!yourform!fldB Is Null) AND ...

In a compound expression that uses both AND and OR operators, Parentheses are necessary to ensure that the expression is evaluated as you intended. The sample expression allows for null values in the criteria fields so that either or both will be used. It also has the side effect of returning the form fully populated which may or may not be of value. I don't ever want the form fully populated so I actually do something slightly different but I didn't want to complicate the example. I also add a Clear button that simply sets all the criteria controls to null so the user doesn't have to futz whith them to perform a new search.

When you get to the point of wanting to analyze the data, you will be very happy that it is properly normalized rather than flattened like a spreadsheet. The analysis can be done with simple "Totals" queries rather than complex expressions and VBA. You might want to look into CrossTab queries (there is a wizard to help you build them) which will allow you to view the data flattened like a spreadsheet but you will not be able to update the data in this view it is simply for reporting. I have attached a database to demonstrate what I call a "bound denormalize form". I don't know what your level of expertise is but you might find it handy.

 

[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..