473,239 Members | 1,583 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

Opening a 2nd form with form button using data displayed in first form

Hi,

I have an access database and created two forms. The first form looks up Assets by inventory number and shows the student the assets are assigned to. The second form shows the student and all assets that are assigned to them. Sometimes I only have the asset info and want to be able to lookup all of the equipment the student has on the second form. Both forms currently use a Combo box for selecting the asset record or the student record depending on which form you are in. The problem I am having is that the student form requires the Combo Box to be filled in before it will show a record so I cannot just create a form button to call a specific record as it just gives me a blank form which I can then manually fill in the Combo Box value. I want to automate that part of the selection. The combo box contains the student name in LAST, FIRST format as well as the STUDENT NUMBER to ease selecting the correct student when they have the same name.
I am using MS Access for Office 365 v16 64-bit on Windows 10.
May 27 '19 #1

✓ answered by NeoPa

I do like it when new members post questions properly with care and that fundamentally make good sense.

To answer your question I would say that there are a number of possibilities. Sorry, but there you are.
  1. If the form is already open then you can either apply a filter or navigate to the correct record using code.
  2. If the form is opened only when you select the item you want from the other Form then you can either open it with a filter (WhereCondition parameter) or navigate to the required record once it's been opened.
    Some people prefer to pass the info in the OpenArgs property when opening the Form and letting the Form sort out navigating to the required record, but others prefer to handle this in the same code that opens the Form.
  3. It may be that you have particular reasons for using two separate Forms for this, but if not then you may choose to manage it all with Cascaded Form Filtering instead. It seems more straightforward to me, but I don't know your project so there may be good reasons for doing it differently.
Let us know if you need further help with any of these suggestions.

6 1621
NeoPa
32,554 Expert Mod 16PB
I do like it when new members post questions properly with care and that fundamentally make good sense.

To answer your question I would say that there are a number of possibilities. Sorry, but there you are.
  1. If the form is already open then you can either apply a filter or navigate to the correct record using code.
  2. If the form is opened only when you select the item you want from the other Form then you can either open it with a filter (WhereCondition parameter) or navigate to the required record once it's been opened.
    Some people prefer to pass the info in the OpenArgs property when opening the Form and letting the Form sort out navigating to the required record, but others prefer to handle this in the same code that opens the Form.
  3. It may be that you have particular reasons for using two separate Forms for this, but if not then you may choose to manage it all with Cascaded Form Filtering instead. It seems more straightforward to me, but I don't know your project so there may be good reasons for doing it differently.
Let us know if you need further help with any of these suggestions.
May 27 '19 #2
Thanks NeoPa.
I looked through many of the options that you have indicated and was being stumped due to the second form requiring an entry into the ComboBox. I realized shortly after posting that I was populating a text field based on the selection and then the form was populating based on the text field value. By skipping the ComboBox I was able to resolve my problem by;
Setting a TempVar in the original form and then call the 2nd form from the first.
I then set my text box on the second form to my temp value in the 'On Load' Event. I would then automatically receive the correct record data.

I will flag your answer as the best one. Thanks again.
May 27 '19 #3
NeoPa
32,554 Expert Mod 16PB
You may want to consider using the OpenArgs parameter instead. This is an option available to you when opening a Form that allows you to pass information from the caller to the callee. It's preferable to the use of a TempVar as the scope is more appropriate (It doesn't hang around after it's needed cluttering up the database).

My preferred solution, unless you actually need to navigate between different records from within the second Form, is to filter it to that one record when opening. It involves the least code and makes it very clear what your code is doing.

All that said, it's obviously up to you how you play it. I wish you the best of course, but my main desire is to ensure the possible solutions are up and visible to all who seek.
May 27 '19 #4
Thanks for the follow up. I have added a Remove TempVar after I open the 2nd form for the moment until I can get a change to learn more about OpenArgs and implement it that way. Being a Noob I usually land on the first thing that works even if it is not the most elegant but I do like to learn how to improve. Luckily my Databases are for my own consumption at the moment. I am moving towards making them available to others so am always striving to improve upon them.
May 27 '19 #5
NeoPa
32,554 Expert Mod 16PB
Good for you. I started that way (I guess most do). I was just using Access for data manipulation then started playing with Forms etc and realised how fabulous it could be. My first couple of years on here caused me to find out about a whole bunch of things because people had questions about them that I felt I wanted to answer. Once I'd been doing that for a while I found I had become 'the expert' and was very well equipped to take on serious projects.

I've certainly enjoyed the ride :-)
May 28 '19 #6
zmbd
5,501 Expert Mod 4TB
Good Morning Zork182,
Just another approach to cascading forms, combo-boxes, etc...
Parent/Sub-form

I've attached a little example that I whipped up...
It's not a polished application; however, I hope that you are presently surprised with what can be done with some simple default objects, some minor normalization, very basic queries, a few subforms, and no vba. (Hmmm... certain expert (Rabbit ;-) ) here got me on the hook to look for non-VBA solutions)

There's a ton of functionality that I'd put in place; however, this is just a jumping point.

A couple if minor notes:
+ The database opens to the main navigation form
-- This is a special Access Object, called amazingly enough, a Navigation Form
-- This is NOT a tabbed form in anyway shape or form

+The navigation pane is minimized on open, press [F11] to see the underlying tables and forms

+ If you look at the tables if you see in the description
"Which Student is the widget assigned to (1/2 S2WKey)"
"Which widget in inventory is assigned to the student (2/2 S2WKey)"
This indicates that the fields (in this case) are part of a compound key named [S2WKey].
I do this to prevent duplicating records (in this case S2WKey is such that it prevents assigning the same inventory item to the same student multiple times). So it's worth taking a look at the table index properties. I've also set it up so that the same inventory item cannot be assigned to more than one student... yet, I've built in the ability to do so should there be a need in the workflow.

Play with it... just please keep in mind I bodged this together in under hour with three children floating in and around my desk :)
Attached Files
File Type: zip Bytes_972687.zip (226.6 KB, 50 views)
May 30 '19 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: lorirobn | last post by:
Hi, I have a continuous form which displays all the records on a table. The detail row has a command button which, if pressed, takes the user to another form where that record can be edited. ...
0
by: philipfrancisperry | last post by:
I have populated a datagrid successfully using the below code. Sub Fill_table_requests_to_be_allocated() With SqlCmd_get_allocations ...
5
imrosie
by: imrosie | last post by:
Hello any expert out there. I'm yet another newbie (yan) who needs lots of help. Here's my issue. I'm trying to create an product ordering database. I set up a 'Search' form with the ability to...
0
by: =?Utf-8?B?Q2FybCBEZUxhZ2U=?= | last post by:
I have a form and a subform. The relationship is a 'one' in the form to the 'many' in the subform. There is a control in the form that has text that I need to use in a subform comparison. Whenever...
4
by: dstorms | last post by:
Hi, I'm trying to create a form with a list box with 2 columns and a button that opens the selected record in the appropriate form. In the sample below, the first column lists the type of equipment...
3
by: beemomo | last post by:
I have two tables - 'Client' and 'Company', and two forms, 'Client' and 'Company', the relationship between two table is 1:many where a company can have one or more client but each of the client...
5
by: caconklin | last post by:
Access 2003, Windows XP. Currently I am able to open a form - case form - based upon information input by the user in another form - search form. I would like to be able to open the case form...
7
by: rleepac | last post by:
Newbie here...I've been reading these forums for hours and I'm learning so much! I'm a Physician Assistant by trade but necessity has converted me to a pseudo-Access db designer. I have a db that...
17
by: lee weaver | last post by:
I have a form to edit employee data that is a copy of the add employee form ( Which works great) with a list box at the top to select the employee to edit that is susposed to navigate to the correct...
0
by: gailc1969 | last post by:
I have a pdf form. i have a fill in spot for supervisors email. this could be different depending on who is filling in form. i want the submit form button to pick up that supervisors email and send...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.