Keep same record visible on several forms

[IANGRAND]

Hope this makes sense.

I have several forms linked via a series of command buttons. All forms refer to a central table. On the first form the user enters a UPRN (Unique Property Reference Number) and this looks at a central address database and pulls up the corresponding address.

When selecting the next form via the command button, the UPRN has reverted back to the first record, meaning the user has to search the record for the correct UPRN. Is there anyway to keep the same record on all forms until i decide to select a different record?

 

[kjv1611]

Possibly using a recordset to pull the first record in a recordset? Using it something like this?

Form_Load()
Dim db as ADODB.Database
Dim rs as ADODB.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordSet("tblYourTableName",dbOpenDynaset)

rs.MoveLast  [GREEN]'to move to the last record edited

It's just an idea.. if it works in one form, though, you could just put it in a Access Module, and name the module something like basPullRecord, then put it in a Sub in the module, something like the above with the added:

Option Compare Database
Option Explicit

Private Sub LoadCurrentRecord()
~code from above
End Sub

Then in each form, do this:

Private Sub Form_Load()
    Call LoadCurrentRecord
End Sub

This is more or less just me stabbing in the dark, but I thought it could help you consider a possible avenue.

I basically took information that I use for some other details, and tried to match it to your situation.

One other idea, I'd search the FAQ's, b/c I would think that someone has created a FAQ for your exact problem.

HTH,

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[IANGRAND]

Thanks for the response, but im not sure that will work, ill explain further

ie if i am looking at record 2222 on form 1, then open form 2 i would like it to automatically be showing information on record 2222.

Cheers

Ian

 

[kjv1611]

I would think using a recordset should work. There may be a better way, but I'm pretty sure the recordset method could work. Here is a couple idea's:

First, if it will always be the last record updated, then you can just do as above, b/c when the record is updated from the first form, all other forms (assuming you open one first to update the record, and the others later), then the last record updated will be that record. So the rs.MoveLast would place focus on the last record. If you don't have a terribly high number of controls on each form, then you could show the values on the form like this:

Private Sub Form_Load()
Call LoadCurrentRecord
cmbCombo1 = rs.Fields("FieldName1")
txtText2 = rs.Fields("FieldName2")
cmbCombo3 = rs.Fields("FieldName3")
End Sub

Something like that.. or, if you want to declare Public variables, you could Dim public variables to match each of the fields that will show in the forms.. I have one form where I Dim String variables, then set the string variables equal to the controls on the form, move to next record, and set the controls equal to the string variables. So, with multiple forms, you could do a similar method by using Public variables, kind of like this maybe...

'In a module:
Public strField1 As String
Public strField2 As String
Public strField3 As String

'In the first form:
Private Sub Form_Unload()
   strField1 = cmbCombo1
   strField2 = txtText2
   strField3 = cmbCombo3
End Sub

'In other forms which will pull same data
Private Sub Form_Load()
   cmbCombo1 = strField1
   cmbText2 = strField2
   cmbCombo3 = strField3
End Sub
'... and so on

Of course, this is assuming you use the same fields for each table, but I would assume you are using different fields, which is why I would think using a recordset would be the best way to go. If need be to be more specific, you could use an If Then statement to pull just the selected record.. Add in something like this:
If rs.Fields("Field1") = cmbCombo1 Then
~run code
End If

Of course, you would need to put that within a loop which loops through the recordset until it finds the code, then possibly an Exit statement within the If statement to exit the loop once the record is found.. like this, possibly:

rs.MoveFirst
Do While Not rs.EOF
If rs.Fields("Field1") = cmbCombo1 Then
'run code here for setting values for your particular
'form (whichever fields need to be called for this form..
Exit DoLoop (not sure of exact format/term usage here)
Else
rs.MoveNext
End If
Wend

Hope it works out for you, or if anyone else posts a better idea, I'm all ears myself, as I want to continue learning as well.. [BIGEARS]

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

By the way, if you use the recordset method, try using like this instead of the way I put it (memory loss I guess) [WINK]

Dim db as DAO.Database
Dim rs as DAO.Recordset

I was just looking at something else here at work, and it looks like you can't Dim something as an ADODB.Database, well, if someone else knows better, I'm all ears, of course..
[BIGEARS]

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[missinglinq]

Another possibility would be using tab controls rather than separate forms. Each tab or page would still be on desired record. Just a thought.

The Missinglinq

"It's got to be the going,
not the getting there that's good!"
-Harry Chapin

 

[kjv1611]

Very true - using tabs. You can put the controls you want for each option on one tab (make sure you select the actual "tab" of the page associated with the tab if you copy/cut and paste, if not, it will be on the main form. I use tabs in a database I have, and it works great.

Just for example on how the idea works (with tabs), I have "Statuses" for accounts (Promise, Broken Promise, etc.) And depending upon the status of a given account, a particular tab will be set to visible (pageBPS.Visible = True), and any other tabs set to not visible (pagePRM.Visible = False).. So, only the controls you want will be visible. You can also change the style for a tab set to "none" as apposed to "tabs" or "buttons". Of course, if you want to be able to just click to each group of countrols, you could leave it in the default tab format. This works without having to have subforms, saves space, and works much quicker b/c of not having to access separate forms/subforms.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV