Synchronizing From a popup

[Greyfleck]

I have two forms. A property form and 'select address' popup form. If they select an address from the pop up I wish to populate the main form with their selection. I have beaten myself up over this for far too long and would really like some help. I have tried it with a query and requery, AND as it is now, having the popup as modal and changing the rowsource based on the data eturned all without success.
I am happy to email the zipped (96k) extracted database if anyone can help.
Many Thanks
Jon

 

[PHV]

Populates the needed fields of your property form in an event procedure of your popup form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

If you open the pop up form as dialog code execution stops. So this is how I do it.

from your main form
1) open the pop up as dialog
2) define a public variable in a standard module
public glblDate as date
3) from the pop up have some event such as after update save the date to the variable glblDate
4) close the pop up
5) execution returns to your subroutine that called the pop up to open
6) set your field to the glblDate

 

[Greyfleck]

Hi PHV, not from an event on the popup but in code once the popup has closed and control returned to the properties form. By making it Modal, processing stops in properties until the popup is closed. the code is

StDocname = "Addresses"
DoCmd.OpenForm StDocname, , StLinkCriteria, , , acDialog, StlinkArgs

Dim sSQL As String
Dim bWasFilterOn As Boolean

bWasFilterOn = Me.FilterOn

If Me.RecordSource <> "Properties" Then
   Me.RecordSource = "Properties"
End If
sSQL = "SELECT DISTINCTROW Properties.* from properties " & _
"WHERE properties.addresslink = " & Me.NewLink & ";"
            
Me.RecordSource = sSQL

I lifted this from the internet but it seems that I am missing something ....
It fails saying that it cannot find addresslink 3(newlink which I know is there ..

 

[Greyfleck]

Hi Majp, my problem is that I need to refocus on a new record rather than just a field. please see the code attached and passed to PHV....
many thanks for your help

 

[PHV]

So, set the RecordSource of your property form in an event procedure of your popup form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I am not sure if I understand "refocus on a new record", but maybe you mean

... properties.addresslink =" & glblAddress

Sorry, I misread you where popping up an address form not a date form. So change what I said to

public glblAddress as string

 

[Greyfleck]

Hi PHV, please excuse my ignorance. Can u pls provide an example of what you mean ...

Jon

 

[MajP]

Note. Both my suggestion and PHV suggestions work. If I build popups they are usually called from many other forms so I will often usual a "global" variable or pass to the form the name of the form or the control that called the pop up. This provides a lot of flexibility and reuse. There is nothing wrong with using global variable at the right time and place (as long as you strive ,in general, to limit the lifetime and scope of all variables).

 

[PWise]

This is the way i do something like this

1) open the pop up as dialog
execution of main form code Stops
2)Select the address on the pop up
3)on a command button click
me.Visible= false
execution of main form code resumes
4)me.address = forms("popupform").address
5) docmd.close acForm,"popupform",acSaveNo
.......

 

[Greyfleck]

All, sorry if I have have misled you as your responses all are steering towards a field being returned from the popup. I use the popup to display/select the first line of the address they wish to use. I then return to the main form with the key (addresslink which works) of the properties table which I then need to use to go to the correct record in the table and then display it. it is the repositioning and record displaying that I am having problems with ...
apologies ....

jon

 

[MajP]

maybe

dim rs as dao.recordset
set rs = me.recordset
rs.findfirst "addressfield = '" & me.addresslink & "'

 

[Greyfleck]

Hi MaJP
I have tried the following:

Dim LinkAsNum As Integer
LinkAsNum = Val(Me.NewLink)

Dim rs As DAO.Recordset

If Me.RecordSource <> "Properties" Then
    Me.RecordSource = "Properties"
End If

Set rs = Me.Recordset
rs.FindFirst "[addresslink] = " & LinkAsNum

If rs.NoMatch Then
    MsgBox ("big problem")
End If

It returns nomatch even though i have 3 records Addresslinks 1, 2 and 3 and I am selecting Addresslink 3

I have no idea why....

Jon

 

[MajP]

make sure you are returning a good link
If rs.NoMatch Then
MsgBox "big problem. Link = " & linkAsNum
End I

 

[Greyfleck]

By dint of a lucky guess I seem to have a resolution!! I set 'Order by on load' and 'Filter on load' on the data section of the form to No and it now works. I never set them on so assume that they are the default. I should point out that, whilst I have over 30 years of the likes of Cobol etc. Access is new to me and self-taught. I have no idea why the changes I made have worked but am just happy that they have!
Thanks MajP, PWise and PHV for your suggestions and getting me thinking.

Jon

 

[Greyfleck]

My Cure failed!
I have a table of Properties

Addresslink  Integer Primary Key
Addr1 text
Addr2  text ... etc

There are 3 records
Addresslink 1 thru 3
I call the Properties form with a linkcriteria of 4 as no record exists for this client and I want to give them the opportunity to enter a new record OR call a popup to select previous records (1 thru 3)
The popup performs well and allows them to select record 3.
When I return to the calling program I use the following code to place the selected record on the screen

Dim rs As DAO.Recordset

If Me.RecordSource <> "Properties" Then
    Me.RecordSource = "Properties"
End If

Set rs = Me.Recordset

rs.MoveFirst
FirstEOF = rs.EOF
FirstALink = rs!AddressLink
rs.MoveNext
SecondEOF = rs.EOF
SecondALink = rs!AddressLink
rs.FindFirst "[Addresslink] = " & LinkAsNum  ‘** which has been returned from the popup value 3

If rs.NoMatch Then
    MsgBox "big problem. Link = " & LinkAsNum
End If

The FirstALink gets an addresslink of 4
The SecondALink is not processed as the secondEOF returns eof of true

My problem appears to be filtering.
The records are definately there ....
Can anyone help please

 

[PHV]

Why not simply this ?
Me.FilterOn = False
Me.RecordSource = "SELECT * FROM Properties WHERE Addresslink=" & Val(Me!NewLink)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Greyfleck]

Thanks PHV that has aided me in getting the required record onto the Form. However, I am now in an interminable Access loop that I am sure frustrates all of us. Fixing one problem generates another. Now when I go into the form with a valid LINK of 3 it does not display anything!!!
Any suggestions?

Jon

 

[PHV]

And this ?
Me.FilterOn = False
Me.RecordSource = "SELECT * FROM Properties"
Me.Recordset.FindFirst "Addresslink=" & Val(Me!NewLink)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Greyfleck]

Both of your suggestions work on return from the popup. The problem I now have is when entering the form with a valid LINK (without going to the popup) it doesn't show any data. Is it your suggestion that I put the code on the forms load event?