Linking a form to multiple tables

[amb3r]

Hi Guys,

I have about 10 forms/tables in my DB. I want to be able to get data (from couple of fields) from each of the forms/tables and display it on to ONE form.

Is there anyway i could acheive this. I know one way of doing this is by using a query, and then creating a form from the query. This approach isn't really suitable for what i am looking for. I was wondering if anyone knows a different method, or if there is way of doing it through VBA.

Thank you very much

Kind regards,
Amber

 

[Banana]

Yes.

You don't need VBA. Just create a query pulling all what you need and base your form's recordsource on that query.

If you need to edit, be sure that the query you make is updatable. Search on updatable query to get the details on what makes it updatable and not.

 

[amb3r]

Hiya,

Thanks for your reply Banana man
Yes I know i could do this using a query. However,as mentioned above, this approach isn't really suitable for what im trying to do. So, i was wondering is there any another way of doing this?

Thanks alot,
amber

 

[dsigner]

If you are determined to avoid a query (which I assume includes SQL) then I think you will have to do it by opening the recordset for each individual table and retrieving exactly what you want. I hope the result will be worth all the trouble!

 

[amb3r]

Thanks for your reply dsigner.
Unfortunately, i cant use the query method in this particular case. Its kind of difficult to explain why and also it will take too long to explain!

How can i extract couple of fields from each form/table and input them onto a single form, dsginer? can you please provide more details on your above suggestion.
Trust me, yes it will be worth all the trouble!!
Thanks alot,
Amber

 

[Banana]

An example (I'm assuming you're using Access for both FE/BE here). On Open Event for the form, you need to call this:

'Use Static Declaration to keep the values persistent for all procedures
Static db as DAO.Database
Static rst1 as DAO.Recordset
Static rst2 as DAO.Recordset

'Opens a connection to the tables you need
Set db = Currentdb
Set rst1 = db.OpenRecordset("YourTable1")
Set rst2 = db.OpenRecordset("YourTable2")

Then in OnCurrent Event-

'Load data from Table1 to the fields on form
With rst1
   MyTextBox1 = !FieldName1
   MyTextBox2 = !FieldName2
   MyTextBox3 = !FieldName3
End With

'Load data from Table2 to fields on the form
With rst2
   MyTextBox4 = !FieldName1
   MyTextBox5 = !FieldName2
   MyTextBox6 = !FieldName6
End With

Note that you may need to add some code to move the pointer within the recordsets using .FindFirst or .Seek or .MoveFirst or .MoveNext method before you load the data.

Also, if you want to be able to edit those values, you need to open recordset as dbOpenDynaset.

Set rst1 = db.OpenRecordset("YourTableName", dbOpenDynaset)

To save the edits, use .Update method, and to add a new record, use .AddNew method. All you need to do is reverse the equation you used to load the data. So a sample adding new record would be:

'Saves new data to a new record
With rst1
    .AddNew
    !FieldName1 = MyTextBox1
    !FieldName2 = MyTextBox2
    !FieldName3 = MyTextBox3
    .Update
End With

Be advised that if there is an error (e.g. saving a wrong data type), none will be raised. It simply will be ignored. It is up to you to check each fields' validity before saving to the recordset.

Please note that performance will degrade when you have multiple recordset loaded in the memory and when all are open as dynaset. Be sure to close and clear all recordset in form's unload event. Adjust accordingly to what you need to accomplish.

HTH.

 

[dsigner]

I think Banana has covered the recordsets better than I would have. The main trouble is that there are so many types and versions that you need a book to cover everything.

One thing I would add.
I would create a table with all the fields the form needs and then write a record to that from the recordsets. Then use this table as the source for the form. It is much easier to handle forms when they read from a table or query.
Make sure you delete the record after each use so that you don't get old data and also keep the overhead down.

 

[amb3r]

Hiya guys,
Thank you very much for your replies.

I used the example code which Banana wrote above- thank you very much for that hun.

I have a few queries regarding this. Firstly, for my own knowledge i wanted to know the purpose of 'static', or what does it do? I understand the remaining code but couldnt figure that out.
Also, I wanted to know, shouldn't there be any code to 'close' the record sets or is that not necessary?

Finally, I was wondering if you know what maybe the problem with my code:

Private Sub Form_Open(Cancel As Integer)
Static db As DAO.dataBase
Static sevTbl As DAO.Recordset
Static SICTbl As DAO.Recordset
'Opens a connection to the tables
Set db = CurrentDb
Set sevTbl = db.OpenRecordset("Severance")
Set SICTbl = db.OpenRecordset("SICLetters")

End Sub

Private Sub Form_Current()
'Load data from Table1 to fields on the form
With sevTbl
    Me.txtSevLetterDated = !SevLetterDated
End With


'Load data from Table2 to fields on the form
With SICTbl
   Me.txtSICLetterDated = !SICLetterDated
End With
End Sub

I used the above example code to help me but unfortunately im getting an error message saying: 'Run-time error 424: Object Required' on the line "Me.txtSevLetterDated = !SevLetterDatede ".

I can't figure out why this is. I've tried numerious changes to make it work but with no luck.

Thanks alot,
Amber

 

[Banana]

Amber,

In Form_Current, If you haven't, try and add this and see if you still get the error:

Dim db As DAO.dataBase
Dim sevTbl As DAO.Recordset
Dim SICTbl As DAO.Recordset

Set db = CurrentDb
Set sevTbl = db.OpenRecordset("Severance")
Set SICTbl = db.OpenRecordset("SICLetters")

Static means that the value in variables will be persistent; that is they won't change between subroutines. Ordinarily a variable that is Dim'd in one subroutine is limited to just that subroutine and won't carry over to other, even if same name are used.

I was hoping that by declaring those as Static, you wouldn't have to continually refer to the recordset and re-open the recordset for each subroutine, which would be task-intensive. It may be possible that it didn't work. The possible issue is scoping; you want it to be scoped for whole module (the form itself), not limited to single subroutine (On Current Event).

If what I proposed above did fix it, try to declare it put this at the top of the module:

Option Compare Database
Option Explicit

Static db As DAO.dataBase
Static sevTbl As DAO.Recordset
Static SICTbl As DAO.Recordset

'Your subroutines should be below

If that doesn't work either, replace "Static" with "Public", which will allow it to be shared by all subroutine.

Post back with the results and progress.

 

[amb3r]

Thanks alot Banana, you're a star!
As you suggested, I put all the code under the On Current event of form:

Static db As DAO.dataBase
Static sevTbl As DAO.Recordset
Static SICTbl As DAO.Recordset

'Opens a connection to the tables
Set db = CurrentDb
Set sevTbl = db.OpenRecordset("Severance")
Set SICTbl = db.OpenRecordset("SICLetters")

'Load data from Table1 to fields on the form
With sevTbl
    Me.txtSevLetterDated = !SevLetterDated
End With

'Load data from Table2 to fields on the form
With SICTbl
   Me.txtSICLetterDated = !SICLetterDated
End With

It kind of works. It picks up the values from the two tables, which is what i want. However, it picks almost a random value from the tables. Whereas, i want it to only show values for specific records.

Every table has a field called EmpNo. I have a textbox called txtEmpNo on my form. I want it to look up the value of txtEmpNo (e.g. txtEmpNo = 123)and use this to show the data for the records EmpNo=123,from the two tables.

Sorry i know i'm not wording this correctly but hope you understand what i mean! hehe

I think i need to use the .FindFirst method, but don't know how to code it correctly.
I really appreciate all your help
Thanks,
Amber

 

[Banana]

You guessed it right; when you open recordset, you have to tell the pointer where to be at.

If you want to go through every records (which makes sense if you have the recordset based on a query that returns only records that you want to edit)-

rst.MoveFirst
Do Until rst.EOF = True
    .Edit 'Assuming you are editing the record; no need if you're just read the recordset
    'Do something here.
    .MoveNext
Loop

To do a search for a specific record, the easiest thing to do would be to have a combobox on your form with rowsource set same as whatever the master table is- then when you select the record, run the ID through all the tableset:

rst.FindFirst("FieldName = " & Me.cboSelector

Also, you may want to look into creating your own X of Y record buttons to support this; search the forum on "Duplicate X of Y records" for a sample.

Finally, make sure to test that the value you set for your tables is still preserved in other subs- Did you see my suggestion about moving it to the top of module? You need to make sure that all subroutines can access the value.

HTH.

 

[amb3r]

Thanks again Banana

I tried declaring at the top of module but I got some errors so i just left it as it is because i knw atleast it works this way Also for the purpose of what i am doing, I dnt think i need to declare at the top of module because i wont be using it in any other subs.

All i need is for it to lookup the value of the txtEmpNo text box on the current form and match this against the the EmpNo of each of the tables and display the couple of fields i require.

As suggested above, I have used the .FindFirst method to get the speficic record:

Private Sub Form_Current()

Static db As DAO.dataBase
Static sevTbl As DAO.Recordset
Static SICTbl As DAO.Recordset

'Opens a connection to the tables
Set db = CurrentDb
Set sevTbl = db.OpenRecordset("Severance")
Set SICTbl = db.OpenRecordset("SICLetters")

'Load data from Table1 to fields on the form
With sevTbl.FindFirst("EmpNo = " & Me.txtEmpNo)
    Me.txtSevLetterDated = !SevLetterDated
End With
'Load data from Table2 to fields on the form
With SICTbl.FindFirst("EmpNo = " & Me.txtEmpNo)
   Me.txtSICLetterDated = !SICLetterDated
End With
End Sub

However, the .FindFirst method is still not working :'(
grrr it's really annoying me now I know it's something simple but just can't get it to work.
Thanks for your help hun

 

[Banana]

Amber,

If you're not going to use it in any other subs, you don't need to have it set as Static; just use Dim. I thought you would want to use it in other subs as you would need to save or edit the form which would need that information.

Also, it's helpful to explain what kind of error you get when you run the code. There's several reasons why things can go wrong and telling us "it didn't work" isn't really helpful.

That said, you can test your variables in couple ways:

1) add a breakpoint by clicking on gray column to left of the code. Use F5 to step through the code

2) hover your mouse cursor over the variable. VBA will pop up the value that variable has. It doesn't work for objects, though.

3) To get value from objects not available from hover over, use this code:

Debug.Print sevTBl.EmpNo

Insert this both before and after the FindFirst to observe how the value change.

Report back with the results.

 

[amb3r]

Hi guys,

Sorry i've not been able to post back as i've been on holiday for just over a week. God it feels weird to be back at work!

Anyway back to this...
Sorry Banana, i thought i had wrote the error message. I was having such a stressful day, didn't know what i was doing.

Anyway just to recap....What i'm trying to do is to show values of a few fields (from couple of tables) where the field EmpNo in each of the tables equals the value of the txtEmpNo text box on the current. These values are only required to be viewed, not edited.
I could get the form to connect to the different tables. however the values it retrieves are just random values, rather than from specific records.
To rectify this, I tried using the findfirst method...

Here is the revised code:

Private Sub Form_Current()
Dim db As DAO.dataBase
Dim sevTbl As DAO.Recordset
Dim SICTbl As DAO.Recordset
'Opens a connection to the tables
Set db = CurrentDb
Set sevTbl = db.OpenRecordset("Severance")
Set SICTbl = db.OpenRecordset("SICLetters")

'Load data from Table1 (sevTbl) to fields on the form
    With sevTbl.FindFirst("EmpNo = " & Str(Me![txtEmpNo]))
    Me.txtSevLetterDated = !SevLetterDated
    End With

'Load data from Table2  (SICTbl) to fields on the form
   With SICTbl.FindFirst("EmpNo = " & Str(Me![txtEmpNo]))
   Me.txtSICLetterDated = !SICLetterDated
   End With

End Sub

Unfortunately, i think the .FindFirst method is not working. I'm getting the following compile error message "Expected function or variable" on the following line:

    With sevTbl[B].FindFirst[/B]("EmpNo = " & Str(Me![txtEmpNo]))

Any help/guidance would be greatly appreciated

Thanks for your time and help,
Regards,
Amber

 

[amb3r]

Hi guys,

Just to quickly update you....I've managed to make this work finally!
The problem seemed to be when declaring the recordsets....I didn't use "dbOpenDynaset" when declaring the recordsets, which seem to make all the difference!

Here is the revised code:

Private Sub Form_Current()
Dim db As DAO.dataBase
Dim sevTbl As DAO.Recordset
Dim SICTbl As DAO.Recordset
'Opens a connection to the tables
Set db = CurrentDb
Set sevTbl = db.OpenRecordset("Severance", dbOpenDynaset)
Set SICTbl = db.OpenRecordset("SICLetters", dbOpenDynaset)

'Load data from Table1 (sevTbl) to fields on the form
sevTbl.FindFirst ("EmpNo = " & Me.txtEmpNo)
    With sevTbl
    Me.txtSevLetterDated = !SevLetterDated
    End With

'Load data from Table2  (SICTbl) to fields on the form
SICTbl.FindFirst ("EmpNo = " & Me.txtEmpNo)
   With SICTbl
   Me.txtSICLetterDated = !SICLetterDated
   End With
End Sub

Thanks
Amber

 

[Adeptus]

(I still don't see why it couldn't be done as a query... or subforms)