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: 3
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,205
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: 4
 
Go to the top of the page
 
tina t
post May 20 2017, 07:58 PM
Post#4



Posts: 4,944
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 May 21 2017, 06:01 AM
Post#5


UtterAccess VIP
Posts: 12,047
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 May 21 2017, 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 May 21 2017, 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: 6
 
Go to the top of the page
 
mike60smart
post May 21 2017, 02:42 PM
Post#8


UtterAccess VIP
Posts: 12,047
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: 4


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 May 21 2017, 06:24 PM
Post#9



Posts: 4,944
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 May 21 2017, 06:32 PM
Post#10



Posts: 4,944
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 May 22 2017, 04:13 AM
Post#11


UtterAccess VIP
Posts: 12,047
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 May 22 2017, 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: May 22 2017, 05:19 AM
Attached File(s)
Attached File  Database11.1__2_.zip ( 32.18K )Number of downloads: 4
 
Go to the top of the page
 
mike60smart
post May 22 2017, 08:11 AM
Post#13


UtterAccess VIP
Posts: 12,047
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: 3

--------------------
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 May 22 2017, 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: 2
 
Go to the top of the page
 
mike60smart
post May 22 2017, 09:04 AM
Post#15


UtterAccess VIP
Posts: 12,047
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: 4

--------------------
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 Jun 12 2017, 10:07 PM
Post#16



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


hello munk, sorry i didn't get back to you much earlier - this thread somehow got marked as "read", so didn't stay at the top of my My Discussions list, and i just overlooked it. don't know if you're still working on this, but in case you are:

i took a look at the .mdb file you uploaded in post #14. FIRST, before anything else, open table TTable in Design view, and look at field Main_IDFK. check the field's DefaultValue property setting. if it shows a zero (0), delete the zero! that field needs to be blank, until you set its' value in code.

now, the code in form TTable isn't working because of a minor mistake you made: here's the code you're using in form TTable's BeforeUpdate event, as

If IsNull(Me!Main_IDFK) Then Me!main_id = Me.OpenArgs

first, remember that this code is running in form TTable. second, remember that the point of this code is to add the primary key value, from the Current record in frm_main, to the related foreign key field in form TTable. so the first thing the code does is check to see if the foreign key field is Null, as

IsNull(Me!Main_IDFK)

if it is null, then the code needs to set the value of the foreign key field it just checked, as

Me!Main_IDFK = Me.OpenArgs

your code is trying to set the value of a field called main_id, and that field does not exist in table TTable.

so put my examples together, and here's how that line of code should read, as

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

also, because the two tables are related, with referential integrity enforced, you need to make sure that the Current record in frm_main has been saved - and so has a stored primary key value - before opening form TTable. otherwise, you won't be able to add a record in form TTable, you'll get an error. so, suggest you change the code in frm_main as follows

CODE
Private Sub Command15_Click()
  
      If Me.NewRecord Then Me.Dirty = False
      DoCmd.OpenForm "TTable", , , , , , Me!main_id
  
  End Sub

remember, all we care about is that the Current record in frm_main has been stored in table main, so that the record's primary key exists in the table. so the above code checks to see if the Current record is a NewRecord. if it is, that means the primary key value is not stored in the table yet - not good. so then the code sets the form's Dirty property to False - this effectively saves the Current record to the table. then the next line of code opens form TTable.

if the Current record in frm_main is an existing record (not a NewRecord), then the record's primary key value is already stored in the table, so there's no need to force a save before opening form TTable. but if you'd like the Current record to always be saved, whether it's a new record or not, then you can change that first line of code to

If Me.Dirty Then Me.Dirty = False

also, different subject: i looked at the tables' relationship in the demo db. i noticed that you checkmarked the CascadeUpdate option. you don't need it, because the field main_id is set as an Autonumber, so it can't be changed once it's assigned. since it can't be changed, there will never be a need to update the foreign key value in field Main_IDFK. that's one reason i always use surrogate primary keys in my tables, so related foreign key fields will never have to be updated.

hth
tina
This post has been edited by tina t: Jun 12 2017, 10:25 PM

--------------------
"you can't take the sky from me"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd June 2017 - 03:32 PM