From listbox to data sheet form... rowsource to recordsource

[morglum007]

From listbox to data sheet form... rowsource to recordsource (solved)

Good day,

Here I am again with the eternal problem of search forms.

Well, as I could not solve the listbox problems due access limitations, I decided to change "query" to a data sheet form, and use the listbox as a "preview" of what the user is searching.

I have a form, called "General form", in which there are three cascading comboboxes. First select all database tables, second one all fields in such choosen table and third one, values you want to be filtered in such field.
The result is a "search" statement in listbox that will show specified data.
First combobox is named "elementscb", second one "fieldcb", and third one is "finalchoosecb", saving data in tempvars format to allow form exchanging without loosing such variables.

Final statement in listbox (named "searchlist") is working fine:

Me.Searchlist.RowSource = "SELECT " & elementscb & ".* FROM [" & elementscb & "] WHERE [" & fieldcb & "] = finalchoosecb.text"

Once there, I decided to try showing such data in a datasheet in order to avoid listbox limitations, and made another form (which I set as continuous form) named "Results". There is a "View details" command button that will launch this form.

I have set the next in results open event:

Private Sub Form_Open(Cancel As Integer)
elementscb = TempVars("elementscb")
fieldcb = TempVars("fieldcb")
finalchoosecb = TempVars("finalchoosecb")
MsgBox elementscb
MsgBox fieldcb
MsgBox finalchoosecb
Me.Form.RecordSource = "SELECT DISTINCT [" & elementscb & "]![" & fieldcb & "] FROM [" & elementscb & "] WHERE [" & fieldcb & "] like'" & finalchoosecb & "'"

I am getting the correct values, but I think I am not using the correct statement for data sheet source, because I always get a "blank" data sheet with no data.

I have changed this statement many times in many ways, but no luck yet.

Any ideas???

Thank you so much. I really appreciate any reply.

Morg

 

[DCrake]

So combo box 1 holds the table names and combo 2 hold the respective field names and combo 3 is the critera. Then you want to filter based on the combinations.

On the after update of the fields combo

Dim sStr As String
Dim TmpTable As String
Dim TmpField As String
Dim TmpCriteria As String

TmpTable = Me.Combo1
TmpField = Me.Combo2
TmpCriteria = "Like *" & Me.Comb3 & "*"
sStr = "Select " & TmpField & " From " & TmpTable & " Where " & TmpField & TmpCriteria & ";"

Me.Lstbox.Rowsource = sStr

May not have the logic correct but the syntax is correct. If the source of the data is from only one table you do not need to reference it each time. As long as it is in the From section that will suffice. Also you only need to use [] if the fields have spaces in them or conflict with Access naming conventions.

On tip that I may pass on, and this is from the end users point of view, is that the naming conventions used in your tables may not be easily translated as to the contents. This goes for the tables too. Can I suggest you create a new table that holds the actual names and meaningful names

TblTables

TblID:ID Autonumber PK
TblActName: Text
TblNiceName: Text

TblID : 1
TblActName : TblProducts
TblNiceName roducts


TblFields
FldID: Autnumber PK
TblID: Long FK
FldActName: Text
FldNiceName: Text

FldID : 1
TblID : 1
FldActName : ProdName
fldNiceName : Product Name

etc

Now in your tables/Fields combos you can display the meaningful names but bind the actual names. This is more presentable and professional.

David

 

[morglum007]

Thank you so much for the reply Crake,

Well, listbox is working as it should and as stated before, my problem is not the listbox, but the data sheet I open up with command button based on such criteria.

You are right with all you said, yours is more proffesional and mine is more "home made" solution, but as I am not IT technician, it does the job.

In total there are 41 data tables, all of them engineering components with its technical characteristics, so thats why there are so many tables. I used the brackets because data tables names uses spaces, so I must use it also in variables.

I know most of my fields and tables uses not conventional access logic (even the normalization is "wrong" (I would say not conventional), understood the way it should), but I am forced to because final users should understand what I am doing, and most of them are engineers with almost no access knowledge, vba or something like. As I wont be working here for a long time (getting practices), I have set everything as a non IT technician, but as engineer. I need people that will work with this in the future to understand what they are typing, and where, so thats the reason I cannot spend a lot of time in db normalization.

(Note that I am not a data base expert, and I have learnt to "code" in vba in almost 1 month).

In any case, thank you so much for the help. Any tip about how to set up data sheet recordsource??

Morg

 

[DCrake]

Here is another thing to try. After you have effectively built the recordsource store it to a string variable and perform a Debug.Print StrString (Where StrString is the name of your variable). Then go to the immediate window (Ctrl+G) and copy the string to the buffer and then create a new query and paste in the results of StrString. Try and run the query to see if any records are returned. If not and you don't get an error then no records match your filter. If Access thows up an error then there is something wrong with your syntax. Its a case of trial and elimination.

David

 

[morglum007]

Well, thanks again for the reply. This is fast and accurate. Glad to be here.

In any case, as you say syntax "appears" to be correct, because as I said there is a double check with listbox. Listbox is acting as a "preview", so listbox show results correctly.

The problem is data sheet rowsource ( recordsource in this case I think,(please, correct me if I am wrong))

I have seen a lot of recordsource data entry formats, so thats why I am asking about how to set 'em. I am not completelly sure that I were using the correct syntax here, and thats why I ask it. I know it is correct for listbox... but the question is... is it for data sheet also?

After doing something like you told to me I obtained this in Data sheet data origin field:

SELECT DISTINCT [Anodes]![Company] FROM [Anodes] WHERE [Company] like 'The nippon corrosion'

which correspond to Anodes table, Company field and The nippon corrosion is the choosed company. Listbox is working, data sheet isnt.

Is that a correct recorsource?

Thanks again

TIP: Sorry for being here as a dumb making you to lose your appreciated time.

 

[DCrake]

So lets review what we have established. The syntax is correct, the logic is correct, data is being returned to the list box. The issue now is appying this sql to the record source of a form that you are about to open via a command button. (Having spent more time reading your post correctly).

Right, first thing we need to do is to create a public variable in a standard module, lets call this variable StrFormSQL and define it as a sting variable.

Public StrFormSQL As String

Next lets go to the view details form that we want to pass the sql string to and go to the forms OnLoad event.

In here we need to type in the following

If StrFormSQL <> "" Then
    Me.RecordSource = StrFormSQL
Else
    MsgBox "Cannot find any records to display or criteria incorrect.",vbExclamation,"View Details"
    DoCmd.Close
End If

So what we have done is to get the sql from the derived cascading combo boxes and saved it to a public string variable. Then when we open the view details form it takes this sql and applies it to the recordsource. Should for any reason the the string is not present or is empty a message box appears lettng the user know there is a problem. You tell them something, even if is none sensical "Houston we have a problem" so that they are aware that what they have done is not correct.

If you have any further issues let me know.

David

 

[morglum007]

Well, here I am again.

No way, it seems I got a poltergeist.

(TIP: Sorry in advance for my speech if I sounds quite incorrect. I am not english native.)

I did the following on the commnad button:

elementscb = TempVars("elementscb")
fieldcb = TempVars("fieldcb")
finalchoosecb = TempVars("finalchoosecb")
StrFormSQL = "SELECT DISTINCT [" & elementscb & "]![" & fieldcb & "] FROM [" & elementscb & "] WHERE [" & fieldcb & "] like '" & finalchoosecb & "'"
MsgBox StrFormSQL
DoCmd.OpenForm ("Results"), acFormDS

So it passes StrFormSQL as you stated correctly.

Then I load the "Results" form and in the onload event appears:

Private Sub Form_Load()
If StrFormSQL <> "" Then
   Me.RecordSource = StrFormSQL
Else
   MsgBox "Cannot find any records to display or criteria incorrect.", vbExclamation, "View Details"
   DoCmd.Close
End If
End Sub

As if I insert a msgbox into load event it wont pop up, I do it on the open event instead, to see if public variable is right, and of course it is.

So, here we got two different rowsource/recorsource statements.

For listbox rowsource:

Me.Searchlist.RowSource = "SELECT " & elementscb & ".* FROM [" & elementscb & "] WHERE [" & fieldcb & "] = finalchoosecb.text"

works ok

For data sheet recordsource, the above doesnt work, so I must re-write this way:

"SELECT DISTINCT [" & elementscb & "]![" & fieldcb & "] FROM [" & elementscb & "] WHERE [" & fieldcb & "] like '" & finalchoosecb & "'"

This way makes a correct statement, like:

me.form.recordsource = SELECT DISTINCT [Anodes]![Company] FROM [Anodes] WHERE [Company] like 'The nippon corrosion'

but of course, still not working.

I am missing something and I dont know what the hell is.

Thank you Crake for your patience.

 

[DCrake]

Ok, So immediately after you apply the rowsource to the list box type in

StrFormSQL = Me.LstBox.Rowsource

Now try and reopen the view details form. One thing of not is the controls you are displaying on the view details form, does it contain controls that match the ones found in the recordsource of the form?

If it does not work this time send me a cut down version of the app for me to look at.

David

 

[morglum007]

No luck yet.

So, here I am again, and here it is a very cut down version of this part of database. Just one stupid table, and related forms to work with.

Hope I havent forgotten anything.

Still with this stupid table no luck.

I am sure it is a very stupid thing, but enough to drive me nuts.

Thank you so much again for your patience and time.

Regards

 

[DCrake]

Can you send it pre 2007, don't have it on this machine at present.


Afternoon activity is labourious at present if not online it's because I can't log on.

David

 

[morglum007]

Sure, no problem, here you are.

Currently I am unavailable after 13:30 GMT-1 (Paris/Madrid official hour), so dont worry about.

Hope everything is ok, and glad to meet you.

Best regards

Again, sorry for being an ass pain.

 

[DCrake]

Spotted you problem straight away. You have no fields in your view details table. Even if you want to display the data in datasheet mode you still need to have the source fields present in the form. To prove this use the wizard to create a form with a default datasheet view. Select any table and step through the wizard. When saved go in to the design mode and you will see that controls are present as you would expect to see them as a single form, but when you view the form it takes the format as datasheet.

Because, this is only a view results form and as such it is uneditable I see no reason for having it as the user can already see the results in the list box on the calling form.


I am also struggling with the TempVars() as this is not backwardly compatable with 2003

David

 

[morglum007]

Well, as I said in the beginning, there are about 41 tables, and no one have the same fields amount, so thats why view form should have a data sheet format.

As I already said before, I am using this form because listbox limitations. In the case I have sent to you, there is just one table with 3 fields. Now consider 41 tables with 190+ fields in some of them. I need then,a view form to allow users check the values they filtered previously. Listbox will remain as a "preview" window. Later on I could insert a subform, but obviosly, I need before to make a standard data sheet form to work.

Because listbox got in troubles when managing more than 20 fields, I must go this way. It is the only way I have found around to solve my problem.

I already know 190+ fields (but below 255) are too much fields to be managed, but as I stated some post ago it is a must, and I cannot normalize it much more, to make understable to most engineers here.

Then, as you found the problem, I should code a way to read fields, insert them on data sheet table and show results dinamically, right?

I knew it will be difficult, but indeed, not so much. I was wrong.

Is that approximation right?

Regards

 

[DCrake]

Without first creating a form for each table then loading the appropriate form, with the relevant fields I cannot see a obvious solution. You see you have to work on the worse case senerio whereby I mean the table with most amount of fields. Now this is of the top of my head and as such untested. You could possibly create one form based on your largest table them make all the fields unbound (no control source). Then once you apply the recordsource to the form you would iterate though the fields in your recordset and apply the control source of the field to the control.

You would do this using a loop For each control in form testing if it a textbox and not a label. And stating Me.ControlName = Rs.Name where n is the ordinal number of the field in the table. Once complete it would continue to make the remaining un assigned controls invisible.

Something one the lines of...

By Default make all fields invisible

Dim Ctrl As String
For x = 0 to Rs.Fields.Count - 1
     Ctrl = "Textbox" & x
     Me(Ctrl).Controlsource = Rs(x).Name
     Me(Ctrl).Visible = True
Next

If I can think of anything better or any other reader can offer a solution I/They will let you know.

David

 

[morglum007]

Okay, then, it is much more complicated as expected.

As you stated, maybe I found another solution, but maybe tricky to code.

Thats it:

1.- Select a table
2.- Generate dinamically a form of such table
3.- Call it and fill in with wanted data
4.- Once done, and exit, destroy dinamic table.

Is that possible? Just an idea, but remember I am not an Access expert.

Any hint?

Regards

Morg

 

[DCrake]

As far as I am aware you cannot program Access to create a form like you would experience when using the wizards.

This is one of those situations when I say to myself "There must ba an answer, I do not like being beat." Leave it with me, see if I can come up with something.....


Having said that how about exporting the data to an excel spreadsheet then opening up excel to view the data. As it is now in Excel they can play with it there without affecting the live data in the Access tables. This process is fairly simple and easy to code. Let me have you thoughts.

David

 

[morglum007]

Okay, I will trust you.

Meanwhile I'll keep thinking about. It is better two thinking that just one: I dont want to make others work in something that it is just my problem.

So happy my questions are not so stupid as I though at the beginning.

Regards

Morg

TIP: Well, the excel approach is a posible solution, but not so clean (I think). I expect a "standalone" db app, but if not possible, maybe will be the path to be followed.

Add-on to some of your previous thinking:

Whats about creating blank forms in each 41 tables (or those ones needed), and then refering to them. You said thats possible. Maybe a prehistoric solution, but at least will maintain control under access, with no other external app working. Coding it will be little boring, but will do the job.

It will be that way (correct me if I am wrong):

1.- Select table
2.- With an if statement select correct form (or just dlookup in a form's index table)
3.- load such table
4.- Fill in with filtered data
5.- close "report.

Regards

 

[DCrake]

Actually the conversion from Access to Excel is seamless, all the user does is what they are doing now then clicking the view details button and instead of opening another form that contains all the data it copies the resulting recordset into a new Excel workbook and opens up Excel for them to view the results. They then exit Excel and they are automatically returned back to the calling form. When the form is closed it checks for the instance of the Excel workbook and if it finds it it will delete it. So as not to clutter up the folder. Obviously the last step is optional to you.

David

 

[morglum007]

It is a good approach, but it got a big security problem. This database is a protected one which will be expected to be on a server. Server will have limited access, and of course, with multiple users viewing reports that could be a chaos.

In any case, the problem is not setting up the server, but to make excel to boot up locally, and saving restricted data in local comp (even temporally).

By now, this option is risky, and maybe not possible in my case.

Whats about the previous post tip?

Thanks for your patience.

Regards

 

[DCrake]

You can take that approach by creating 41 forms based on the underlying tables without any filtering then when you click on the view details you will need to determine which form to open, maybe a lookup in table or a select case statement. Then by using the original syntax provided the form will display the relevant fields with the relevant filtering.

The only ongoing situation is when or if you make amendments to tables, such as adding or deleting fields you will have to sync up the relevant form(s).

Test the functionality with the largest table first for speed and accuracy.

In respect to security, you will need to make the forms read-only so that they can only view it.

David