Trouble Opening Form To Current Record (1 Viewer)

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
Hi
I had this working but seem to have done something, which I can't figure out, that has changed that.

I'm trying to set up a database that will be easy to add forms to. To do this I have set up a table with all the form names, the title to appear in the header, and the record source.

The set up is very similar for all the forms. There is a continuous form showing the data, and a single form that I use for adding records or editing records.

The problem is opening that form when I want to use it to edit records.

The edit form (single form) is opened when textbox A is clicked. I want the single form to open on the record that was clicked. The code that I am using to do that is
Code:
Private Sub A_Click()
  strTitle = DLookup("Opens", "tblNavigation", "Title ='" & strTitle & "'")
  strTitle = strTitle & "InputEdit"
  DoCmd.OpenForm Title(), , , "A= '" & Me!A & "'", acFormEdit
End Sub

The first two lines deal with getting the name of the form to open and they work fine. This is just a test to get the code figured out so I'm using a test table for data.

A is the field in the table that contains the data I want to match. So, when I click on the field in the continuous form that contains A, I want it to open the single form to the same record that I clicked on.

Me.A is the value of the of the specific text control I am clicking on in the continuous form.

The recordsource for the form is set using
Code:
  Me.Form.RecordSource = strRecSource

Where strRecordSource is a variable defining the recordsource

The problem is that all of that opens the single form at the first record in the table and not the one that I click on.

Thanks for any advice you might be able to offer
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:03
Joined
Oct 29, 2018
Messages
21,358
Hi. I think what you could try is use the Filter and FilterOn properties of the form after assigning a record source to it.
 

isladogs

MVP / VIP
Local time
Today, 16:03
Joined
Jan 14, 2017
Messages
18,186
Not sure why you think it helpful to save the form title, header and record source in a table.

The standard method is to save the form record source in the form property sheet and the header on the form or in the form load code

Then, assuming A is a text field, use
DoCmd.OpenForm "YourFormName", , ,"A = '" & Me.A & "'"

Using your method, it should be
DoCmd.OpenForm strTitle, , , "A = '" & Me.A & "'"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:03
Joined
Aug 30, 2003
Messages
36,118
I'm confused, you set the value of strTitle but then use Title(). The where condition seems okay. Can you attach the db here?
 

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
HI PBaldy

You're obviously not the only one who's confused. I used Title() because I have a function that returns the value of strTitle so that I can use it in a query. My brain seems stuck on using it! Putting strTitle in it's place yields, as it should, the same results.

How do I attach a database?

Thanks
 

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
Hi IslaDogs
It's a database that is designed to be easy to add to. The way I've set it one can simply go to an input screen, and by entering text, add choices to menus, data entry forms, edit data forms etc. That's why that is all in a separate table.

As mentioned in the reply to PBaldy, strTitle is the same thing as Title(), just a brain fart on my part. My code is the same as yours (I did replace Title() with strTitle)
Code:
strTitle, , , "A = '" & Me.A & "'"
and it always goes to the first record in the table.
 

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
Thanks
I'm being dragged away for dinner. I'll get to it A.S.A.P.
 

Cronk

Registered User.
Local time
Tomorrow, 03:03
Joined
Jul 4, 2013
Messages
2,770
AJJJR
You have the names of all your single forms in a table called tblNavigation and you want to open whichever form corresponds to the value of strTitle which is set somewhere.

You are wanting to position the opened form at the record corresponding to the value in the field A in the continuous form by constructing a WHERE clause
Code:
"A= '" & Me!A & "'"

For this to work, every table would require a field called (literally) A, for that field to be a text field, and presumably indexed as a field having no duplicates.

I must say that for me, your methodology is making it much more complex than needed. I'm with isladogs on using standard methods.
 

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
Hi Cronk
In Fact, I have the names of all the forms, menus, and submenus in a table.
The way I have set it up this line of code is the only one I have to customize for a new form. If I didn't do it this way then each time I wanted to add a new form, I would have have to copy the template, define the form recordsource Define the title of the form, define where the form should go when it returns to the form that called it and modify the menus This way I can go to my navigation table, enter the name of the form, which menu it should appear in, the sql for the recordsource, and everything else is done without wading through a bunch of code (well I still have to add controls to the form, and using them define the statement I am having trouble with). I really appreciate your input but I'm committed to doing it this way and it's working out great. I just have to figure out why this where statement is not working.
 

Cronk

Registered User.
Local time
Tomorrow, 03:03
Joined
Jul 4, 2013
Messages
2,770
Add a couple of debug.print lines to your code to see just what values and SQL are being used
ie
Code:
[B][COLOR=red]debug.print strTitle, Me!A[/COLOR][/B]

DoCmd.OpenForm Title(), , , "A= '" & Me!A & "'", acFormEdit
and
Code:
[B][COLOR=red]debug.print strRecSource[/COLOR][/B]
Me.Form.RecordSource = strRecSource


Run your code and come back with the values generated in the Immediate Window at the bottom of your code screen.
 

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
O.K. So, I did some further checking and I've narrowed the problem down a bit. First I would like to say that it would be easier to just put the Recordsource in the form (it works if I do that) , but at this point I'm wondering why it is doing what it's doing and how to fix it, just as a matter of understanding.

I'm not sure exactly what the problem is but this is what I have discovered.

I'm setting the recordsource, in the OnLoad event of the form with the line:

Code:
  Me.Form.RecordSource = strRecSource

and it doesn't work.

If I copy the exact same sql found in strRecSource, into the Recordsource Property Sheet of the form it works. But when the sql is defined in the form's Property Sheet then the Filter property is set to:
A=(Value of A I clicked on in the Continuous form).

When I set the RecordSource in the OnLoad event, when I look at the Property Sheet the Filter property is blank

(theDBguy suggested the Filter propertymay be involved)

Trouble is that I don't really understand the filter property well, and I don't understand why it's working differently when the Recordsource is defined in VBA .
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:03
Joined
Aug 30, 2003
Messages
36,118
Are you able to attach the db here? It would be easier if we could play with it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:03
Joined
Oct 29, 2018
Messages
21,358
When I set the RecordSource in the OnLoad event, when I look at the Property Sheet the Filter property is blank

(theDBguy suggested the Filter propertymay be involved)

Trouble is that I don't really understand the filter property well, and I don't understand why it's working differently when the Recordsource is defined in VBA .
Hi. Since you mentioned my suggestion, I'll just elaborate on it a little bit.

Try setting the RecordSource property of your form in the Open event, instead of the Load event. For example:
Code:
Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = strSQL
Me.Filter = "FieldName='" & SomeVariable & "'"
Me.FilterOn = True
End Sub
Hope that makes sense...
 

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
I'll have to go over and see how to attach the database. Problem is that everything is pretty rough and it will require some explaining to understand.

I think I have found the problem though. Instead of putting the condition in the DoCommand.OpenForm statement I think I should be putting it directly in the sql.

It will take me a while to figure out how to do that because I still haven't figured out how to convert sql to the string you need and my Allen Browne converter form isn't working. I mean really, It's a lot of quotes!
 

AJJJR

Registered User.
Local time
Today, 09:03
Joined
Mar 19, 2018
Messages
56
theDBguy

Thanks for that. I think that will work but I am anticipating one problem, which is getting the value of the control in a variable. I've run into this before and as I recall it works something like:

You open a continuous form and the first record is the current record. When you click on a textbox control, and assign a value to a variable for that control, in the OnClick event the variable will receive the value of the first record as the current record does not change when you click on another record (hope I have the terminology right).

Actually I just remembered that the solution for that is ,I believe, to use a button to click on and not the control itself.

P.S. It turns out that when you set the recordset with VBA you don't need the quotes.

Thanks a lot, I'll post an update
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:03
Joined
Oct 29, 2018
Messages
21,358
theDBguy

Thanks for that. I think that will work but I am anticipating one problem, which is getting the value of the control in a variable. I've run into this before and as I recall it works something like:

You open a continuous form and the first record is the current record. When you click on a textbox control, and assign a value to a variable for that control, in the OnClick event the variable will receive the value of the first record as the current record does not change when you click on another record (hope I have the terminology right).

Actually I just remembered that the solution for that is ,I believe, to use a button to click on and not the control itself.

P.S. It turns out that when you set the recordset with VBA you don't need the quotes.

Thanks a lot, I'll post an update
Hi. Keep trying and let us know what you find out. As far as I know, once a control gets the focus, by clicking on it, then the current record is changed to represent that row/record.
 

Users who are viewing this thread

Top Bottom