UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Id Help, Access 2013    
 
   
munkroot
post May 19 2017, 08:57 AM
Post#1



Posts: 19
Joined: 24-January 17



Hi All
i have a subform_roles (Continues forms) on my main form, which works great..however i am wanting to move it off the main form and just use a button on the main form to access the roles form

however i am having issues with form IDs. in particular with the new row the roles_ID is ok set to "new" but the main_ID field is blank or shows a '0'

i have tried on the roles form in the main_ID and placed =[Forms]![frm_Main]![main_ID]. this kind of works but as soon as i move to the next record on the main form the roles - main_ID change to that form i am viewing.

the roles table and the main table are one to many related by the main_ID.

i hope this makes sense - i've added some screen shots on


all the best munk
Attached File(s)
Attached File  roles_form.JPG ( 44.67K )Number of downloads: 2
Attached File  relationship.JPG ( 24.07K )Number of downloads: 2
 
Go to the top of the page
 
doctor9
post May 19 2017, 09:50 AM
Post#2


UtterAccess Editor
Posts: 17,076
Joined: 29-March 05
From: Wisconsin


munk,

Based on your screenshot, I'm concerned that your data isn't properly Normalized, which could be the main source of your difficulties.

For example, you should probably have separate tables for Departments, Categories, Work type, Customers, Job Numbers, Addresses, Roles, and logged labor hours. Plus, you rarely want to store values you can calculate from your raw data, so a field named "Total" raises a red flag as well.

Can you describe what sort of real-world data you're working with?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
munkroot
post May 20 2017, 06:44 PM
Post#3



Posts: 19
Joined: 24-January 17



Hi Dennis

sorry i have just got back to you and thank you for your input. yes the DB is all normalized i have the items you described as separate tables etc.
the reason i have not shown to much data as the data is sensitive.

i have done a demo on a new db hope it would make more sense

the only thing i am trying to do is to link a table so i can report on it later down the line so just ignoring the screen shot i sent for the time being and its content

i'll hopfully clear up my question and issue smile.gif

so i just set up the tables below

main
PK main_ID auto number
test data short text
Ttable
PK TTable_ID autonumber
Main_IDFK Number
TTableTest Short text

Relationship
Main.mainID 1tomany Main_IDFK

Forms
frm_main
frm_TTable

frm_Main
enter random data in the Test Data field to generate the main autonumber PK

Insert a button to open TTable display specific data from main.main_ID and TTable main_IDFK
Click button - open frm_TTable

TTable
enter testdata to generate TTable_ID autonumber

now at this point i am not seeing the main_ID in the main_IDFK field. i would expect the form to pick that up from the table or be injected to the main_IDFK because of the 1 to many

now if i made a subform from the frm_TTable and placed it on the frm_main it works ok - i automatically see the main_ID in the main_IDFK field

i have put a screen shot on here to explain and the test db

so my question is how do i show the main_id in the main_idFK like a subform does?

hope this clears my q up

thanks for your time Dennis, really appreciate it

munk


Attached File(s)
Attached File  Database11.zip ( 46.94K )Number of downloads: 1
 
Go to the top of the page
 
tina t
post May 20 2017, 07:58 PM
Post#4



Posts: 4,884
Joined: 11-November 10
From: SoCal, USA


so you have a button, on your main form, that you want to use to open the roles form as a separate form - instead of using the roles form as a subform inside the main form. and you want to link new records you add, in the roles form, to the current record in the main form.

if the above is correct, then it's easy enough. use an OpenForm action in the main form's command button code, to open the roles form, as

DoCmd.OpenForm "TheNameOfTheRolesForm", , , , , , Me!MainID

as the code example above shows, use the OpenArgs argument to pass the current main form record's primary key value to the roles form as you open it. the value is stored in the OpenArgs property as long as the roles form is open. so you can add the value to each new record you add in the roles form. there are several ways to do that. one way is to add a line of code to the roles form's BeforeUpdate event procedure, as

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me!MainID) Then Me!MainID = Me.OpenArgs

End Sub

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
mike60smart
post Yesterday, 06:01 AM
Post#5


UtterAccess VIP
Posts: 12,009
Joined: 6-June 05
From: Dunbar,Scotland


Hi Munk

The subform populates as it should when I enter data ??

Attached File  Main.JPG ( 30.24K )Number of downloads: 1

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
munkroot
post Yesterday, 12:53 PM
Post#6



Posts: 19
Joined: 24-January 17



Hi tina mike thank you for the messages,
yes Tina that is exactly what i am trying to do that brilliant! thank you so much for the post i shall give it a shot and let you know how i get on.

Mike thanks bud, yes the subform works ok i just wanted to show it so you could see the main_IDFK being populated.

Thanks again both
Stars as always


Munk
Go to the top of the page
 
munkroot
post Yesterday, 02:22 PM
Post#7



Posts: 19
Joined: 24-January 17



Hi Tina

ok so i have done as you said it still doesn't seem to work not sure what i am doing wrong. wink.gif

ive uploaded the db for you here with the change to the TTable button on the frm_main and also the If IsNull(Me!Main-IDFK) Then Me!Main_ID = Me.OpenArgs. on the TTable beforeupdate

cheers
munk


Attached File(s)
Attached File  Database11.1.zip ( 72.93K )Number of downloads: 1
 
Go to the top of the page
 
mike60smart
post Yesterday, 02:42 PM
Post#8


UtterAccess VIP
Posts: 12,009
Joined: 6-June 05
From: Dunbar,Scotland


Hi Munk

I modified the Code as follows:-

On your Command Button "TTable" Click Event:-

If Me.Dirty Then Me.Dirty = False

DoCmd.OpenForm FormName:="TTable", _
DataMode:=acFormEdit, _
OpenArgs:=Me.main_id


Also your Subforms Load Event to this:-

If Not IsNull(Me.OpenArgs) Then
DoCmd.GoToRecord , , acNewRec
Me.Main_IDFK = Me.OpenArgs
End If

Attached File  Database11.1.accdb.zip ( 40.18K )Number of downloads: 1


But I cannot for the life of me see why you need to do this when you have a Standard Main / Subform which works as expected.

Why do you need to use Open Args


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Yesterday, 06:24 PM
Post#9



Posts: 4,884
Joined: 11-November 10
From: SoCal, USA


@Mike, the op is wanting to get the second form off the the main form, and instead open the second form as a stand-alone form - thus the need for passing the main form's primary key value to the second form.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
tina t
post Yesterday, 06:32 PM
Post#10



Posts: 4,884
Joined: 11-November 10
From: SoCal, USA


QUOTE
If IsNull(Me!Main-IDFK) Then Me!Main_ID = Me.OpenArgs. on the TTable beforeupdate

hello munk, i can't look at a .accdb format database, sorry - only .mdb format. but take another look at the code i posted, as

If IsNull(Me!MainID) Then Me!MainID = Me.OpenArgs

remember this code is running in the second form, not in the main form. i used fieldname MainID because your first post shows the table relationships, and shows that the foreign key field in tbl_RolesSelect is called Main_ID. so, not sure why your posted code above is referring to two different fields - but i'm assuming that's the cause of the problem.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
mike60smart
post Today, 04:13 AM
Post#11


UtterAccess VIP
Posts: 12,009
Joined: 6-June 05
From: Dunbar,Scotland


Hi Tina

Yes I realised that and I have given the Op an example of how to do it.


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
munkroot
post Today, 05:00 AM
Post#12



Posts: 19
Joined: 24-January 17



Hey guys
thank you so much for your help on this, i really really appreciate it.

thanks for the update mike i'll give it a go in a bit fella

Tina thanks again for the code update, i've converted the file to a .mdb for you hope that helps. ahhh sorry, i had thought you were talking about the test DB i had setup to simplify the issues. ok i'll give it another shot on my Test db again.

Cheers munky
This post has been edited by munkroot: Today, 05:19 AM
Attached File(s)
Attached File  Database11.1__2_.zip ( 32.18K )Number of downloads: 1
 
Go to the top of the page
 
mike60smart
post Today, 08:11 AM
Post#13


UtterAccess VIP
Posts: 12,009
Joined: 6-June 05
From: Dunbar,Scotland


Hi Munk

Don't know what you did but the Open Arg process does not work in the latest version you uploaded.

Here with it in 2000-2003 Format and the Open Args works just great.

Attached File  Latest.zip ( 23.8K )Number of downloads: 1

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
munkroot
post Today, 08:52 AM
Post#14



Posts: 19
Joined: 24-January 17



hey mike

hum, ya know what, i have not got the foggy lol. smile.gif

ok so i just checked it, thanks for that fella yeah it kinda works. however i am looking for the main_IDFK (new) row to already have the Main_ID already entered.

so when a new row is generated on the continuous forms it automatically shows the main_ID in the Main_IDFK. same as a subform would work.

i have attached a screen shot to show you what i mean, i have edited the (New) row in word then took a screenshot of that to show the Main_ID in the Main_IDFK.

i hope this make more since fella.

Tina, Apologies, i sent the wrong file up i had uploaded the one from mikes code not yours, please find attached the updated db in an mdb format

thanks again guys munk




Attached File(s)
Attached File  ttable.JPG ( 31.37K )Number of downloads: 1
Attached File  Database11.1mdb.zip ( 34.43K )Number of downloads: 1
 
Go to the top of the page
 
mike60smart
post Today, 09:04 AM
Post#15


UtterAccess VIP
Posts: 12,009
Joined: 6-June 05
From: Dunbar,Scotland


Hi Munk

We appear to be going round in circles here.

In the attached when the Database opens it opens to the "frm_Main"

When you click the "TTable" Command Button then a Popup Form opens to a New Record with the Foreign Key "Main_IDFK" populated

The Focus is then set to the Control named "TestData"

Attached File  Database11.3.mdb.zip ( 31.72K )Number of downloads: 1

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd May 2017 - 08:10 PM