Use Macro to switch Forms and Choose the Same Record

New Contributor

Hi,

I have a simple, 1-table database.  I want to have two forms, one in tabular/spreadsheet format  and the other that shows all the fields on one screen.  Each form would have a button to bring up the other form.

 

Here's my question.  How do I build a macro to take me from the spreadsheet form and open up the other (full view) form on the current record of the spreadsheet form?  I expect this to be simple but the available commands (FindRecord, GoToRecord) in Access 2019 don't seem to work for me.

 

Help this old dog relearn an old trick!

5 Replies

@Bardiferous 

Which part are you having trouble with, switching back and forth between forms or selecting the proper record in the called form? Or both?

@George Hepworth

 

Here's a small demo. I hope it matches your requirements, but if not, let me know and we'll modify it.

 

Note: the layout of the forms (datasheet vs normal view) is irrelevant to how it works.

@Bardiferous 

 

here is your demo. note that i remove Reference to one of your db and

commented out a function in your Error handler.

OK.  Thanks for the suggestion(s).  The initial one gave me an error on the  error handler.  The 2nd one worked, but I have never used VB.  

 

I have tried to use a macro (button on the footer) to OpenForm and then either (a) GoToRecord or (b) FindRecord.  Really, pretty simple stuff.  But neither will point me to the record that I seek.

 

So, here is my try.  A simple macro in sampleForm will open up FormOne on the desired record, and not filtered to ONLY that record.  

 

Then, another macro [via button on FormOne header] that fails my mission because, while it will choose the correct record in FormTwo, it filters to ONLY that record.  Although some users would want it that way [CICS, other older DBMS systems, etc].

 

Good grief - how did you attach the Zip file?  I cannot do it here.  Drag & drop says Zip is not supported, nor does my bar show any 'Attach file' option.  So here is a Google Drive link for my version of this sample DBMS:  https://drive.google.com/file/d/120c3UY9Ohfpzlyzd72M5Nfq-b6iad3jH/view?usp=sharing

I almost never use macros. They are too basic and simplistic. I am convinced VBA is the right tool for the job.
I'll look at your accdb later today, when I have a few minutes.