Passing child_ID to new post in parent form (1 Viewer)

mort

Registered User.
Local time
Today, 05:24
Joined
Mar 19, 2018
Messages
30
Hello all. Again I come here for help.

I need some help writing, what I think, must be an easy code.

I have a form "frmItems" listing every item in a shop. Connected to this form is "frmItemLocated" which tells where exactly each item is stored.

The "tblItems" is the parent to "tblItemLocated".

On the form "frmItems" i have a button that opens the "frmItemLocated" so one can see where the item is. The code behind this button works nice, and the "ItemID" is passed along with information on the items current location. But i want to be able to move the item around.

I have made a new button on "frmItemLocated". I want to write code so that when this button is clicked you get a new record in the "frmItemLocated", and I want the "ItemID" field to be passed along to the new record so the information about the new location is still connected to the item.

Again sorry for my bad english. Hope you understand the problem.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:24
Joined
Jan 23, 2006
Messages
15,364
Mort,

Create a description of the "business" involved in simple terms in your native language.
Tell us about the shop and the items and what locations may be involved. It would seem that an item could be loaned to someone; could be broken and being repaired; could be broken and has to be replaced; could be lost or stolen or misplaced....

Create the description of the "bigger picture", review it and adjust as necessary.
Then use Google translate to move it from yur native language to English, then post.

Good luck with your project.
 

mort

Registered User.
Local time
Today, 05:24
Joined
Mar 19, 2018
Messages
30
Thanks for your Reply.

I am working in the police and I am creating a database to keep track of different items seized in criminal cases.

The database is basically a big list of seized items. The items table contains every single item with with some additional information. An item could be something like a cellular phone with imei-number XXX or a car with registration plate YYY. The fields in the tblItems is "Item_ID", "Report_ID", "Item" and a few more.

When we seize items in a criminal investigation we have to write down the seized items in a police report. There can be many police reports in a big investigation, each containing a different number of items. And it is important to know what police report each item belongs to. To keep track of this I have the table "tblReports" that contains the fields "Report_ID", "date", "police officer in charge" and so on.

Every item in my database is connected to a police report in the "tblReports".

In the police station we have different rooms for different kind of items. For example drugs got their own room, cars can be located at different inpounds and cellular phones are located in different offices for analysis. To keep track of this I have a table called "tblLocations" (child), connected to the "tblItems" (parent). As you probably have figured out, a car can be moved from one inpount to another, a bag of drugs will be moved from its initial location and sent to analysis, and so on. It is important to know where the item currently is, and also to know where it has been.
For this reason i have a button on "frmItems" that opens the "frmLocation", and the user will write down the the item is located.


I have made data registration forms for the police reports (main form) and items (sub form). From the subform i have a button that opens up "frmLocations" with opening args to show "ItemID" for the selected item. This is important because the "ItemID" is the link between tblItems (parent) and tblLocation (child). They are working perfectly.

I have also made a search form where the user can search for and look at every single seized item. The search form is the main form where you look at and work with the registered data.

On the search form I have a button that opens "frmLocation" in a pop up window with the following code: DoCmd.OpenForm "frmLocation", , , "ItemID= " & Me.ItemID:
This code works fine, and the frmLocation is opened to show the details for the selected item in "frmItems".

BUT an essential part is that you must be able to move the seized item from one location to another. And this is what I am struggling with.
On the "frmLocation" I have a button with this code: DoCmd.GoToRecord , , acNewRec
The problem is that when you click the button you get a new record in the "frmLocation", but the "ItemID" field is not passed along, and therefore the connection between the item and the location is lost.

I need help writing this code so that when you want to add a new record in the "frmLocation" it til automatically pass along the "ItemID".
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:24
Joined
Jan 23, 2006
Messages
15,364
Thanks for the additional info.
Where are you located? Just curious.
Since you have many sites (analysis areas/rooms, impound locations etc) I am assuming you will need a database that supports multi-users who can access the database from multiple locations.
It also seems to me that part of your bigger picture would be the capture of investigative info, but that may just be because of watching too much TV. I know there has been efforts over the last number of years to make law enforcement systems more compatible.
If you have to communicate with other systems of similar or complementary function, then you may have to do more analysis.

You know your environment better than any reader, so you will have to review your setup and decide.

I offered a sample database a few years ago that dealt with swapping equipment from one location to another. It may be of some value just for ideas. The thread is here, and the database is near the end of the thread. You may also get some insight from the dialog within the thread.

You may also find a useful data model in this link of Law Enforcement models from Barry Williams' site.
Good luck.
 
Last edited:

mort

Registered User.
Local time
Today, 05:24
Joined
Mar 19, 2018
Messages
30
I live in Scandinavia.

The idea is that multiple users can access and use the databae at the same time. However the total number of users will be small, maybe 3-4 at the same time. It will not be a web-database. We have an internal network in the station. The database will not be used to communicate with other systems. It will be a stand-alone database, only meant to get control over information related to seized items.

Becauce of network restrictions i am not allowed to download and view the database.

Is this going to be harder to do in VBA than i thought it would be?
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:24
Joined
Jan 23, 2006
Messages
15,364
I was not asking to see your database.
Access is not really geared to web -- the web side is/has been deprecated.

Here is a link to Database Planning and Design info that may be helpful to you.
I strongly recommend you build a model and, working with pencil and paper, test your model with some test data and sample scenarios to make sure your tables and relationships support your "business". Then develop the physical database.

Good luck.
 

mort

Registered User.
Local time
Today, 05:24
Joined
Mar 19, 2018
Messages
30
I have Done all that. For my purposes i feel its a well designed database. But I am a novice programmer and I have to do some googling and asking when i dont know the answer. So there is no code that allows the child ID to be passed along when creating a new post in a related form?
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:24
Joined
Jan 23, 2006
Messages
15,364
I don't know your complete context, but if you have the "child id" you should be able to pass that value to another event.
Can you post the specific code you are working to show the issue? You can use dummy data for any data values.

Did you look at the database in the link I mentioned re swapping?
 

mort

Registered User.
Local time
Today, 05:24
Joined
Mar 19, 2018
Messages
30
On the frmLocation i have the simpel code DoCmd.GoToRecord,, AcNewrec. I need to change this so i get a code where the ID in the related child table gets passed along from the existing record to the new one. I could not look at the sampel database because of restrictions on the work network. But i will bring a laptop to work on monday and have a look. Thanks.
 

Cronk

Registered User.
Local time
Today, 16:24
Joined
Jul 4, 2013
Messages
2,770
If an item can only be stored in one place, I don't see any need for a separate table. I'd just put a combo box on the item form listing all the locations.

If you had a need to track movements of an item, then a separate table would be required to show when an item was booked in/out of a particular location. If so, the easiest approach is to use a sub form that is linked on ItemID ie there is no need to pass ID's around - the system takes care of if.
 

mort

Registered User.
Local time
Today, 05:24
Joined
Mar 19, 2018
Messages
30
The item can only be stored in 1 place. But I want to be able to see the history of where it has been earlier. There is a separate table called tblLocation. The locations table is linked to the items table with an ItemID. I cant make the locations a subform because you view the registered items in an continious form (search form). So my only option, as I see it, is to open the locations form with a button from the items continious form. This works fine if you want to see What location is registered to each item. But when you want to move it to another location you have to make a new post in the locations form for that specific item. So i need to be able to pass the ItemID when adding a new post in the locations form.
 

Beetle

Duly Registered Boozer
Local time
Yesterday, 23:24
Joined
Apr 30, 2011
Messages
1,808
... I cant make the locations a subform because you view the registered items in an continious form (search form). So my only option, as I see it, is to open the locations form with a button from the items continious form...

Just FYI, you actually can have a continuous main form with a continuous sub form, you just need to place them both on another blank form and use a hidden text box to link them. Not saying you necessarily need to redesign what you have now, just letting you know it's possible.

With your current design, you are going to want to use the Before Insert event of the sub form. This will fire as soon as the user starts entering a new record. You would use something like the following (air code);

Code:
If IsNull(Me.ItemID) Then Me.ItemID = Forms!YourMainForm!ItemID

Or, if you're already passing the ItemID as a variable when you open the sub form, just refer back to the variable to get the ItemID (assuming nothing else in your code has changed it or caused it to go out of scope.)

You may also want some code in the Before Update event of the sub form to prevent saving invalid records (i.e. if all necessary fields have not been completed, cancel the update).
 

mort

Registered User.
Local time
Today, 05:24
Joined
Mar 19, 2018
Messages
30
Thank you Beetle. The code looks Great. Being a novice programmer i havent fully grasped the different event types and How to build all the syntaxes. But this forum is a Great place to learn. Thank you again. I cant wait to get to work on monday to test this out.
 

Cronk

Registered User.
Local time
Today, 16:24
Joined
Jul 4, 2013
Messages
2,770
Beetle beat me to it. The attached database is a simplisitic demo
 

Attachments

  • Items.accdb
    492 KB · Views: 57

Users who are viewing this thread

Top Bottom