[Help] - About Tables And Data

[Dragonreaper]

I have encountered a problem... what it is i have a few tables with data and i have created a form and other forms that open in pop up which interact with the main form.. what i would like is that when i make a new record in table1 it makes a new record in say table 2, table 3, ect.. i am really new to access i have been using it for about a week.. lol i am on work placement and i am 17 and i have figured out how to use the program well.. im still learning and i have just hit this one point where i cant get a new record to add too all the different tables maybe you can help me?

i am sorry but the data is classifid so i cant explain what im doing it about..

 

[neileg]

You only need to add new records if you have data to populate, you don't add empty records. The usual way of dealing with this is to use a form/subform setup where Access handles the parent/child relationship. Popups make life a lot more dificult because you will have to used VBA to deal with the relationship.

 

[Dragonreaper]

Hmm Bit confused with what your getting at but what i am trying to do is say for example i got :

--Table1--
Staff ID - Number
Name - Text
Surname - Text

--Table2--
Staff ID - Number
Availabile - yes/no


and i have a form with say the table1 details and then a command button that opens a form and checks the staff id with table 2 and it shows if that person is availble.. i got that far works perfectly only if i have the data already set in table1 and table2 and what i need is when i have Form1 with table 1 details.. i want to create a new record in table with staff ID, name and surname ect.. and then automaticly add a new record in table 2 with the same staff ID to what i have just created..

you understand where im coming from?

thanks for the reply im abit confused by it but i am working thorugh it to see if i can understand to what you are saying

 

[Dragonreaper]

i have taken on your idea about VBA got abit confused what that ment then i realised lol yea hmm i have programmed in VB before so i am kind of fermiliar with the code as of such but im am just wondering how would i make it so on command click it would store Staff ID as a varable? im kinda trying bt i havnt used Vb in a while i say around about 1 year ago.. and then even with the varaiable how would i then input it into the speciffic feilds??

i am thinking it is Text0.Value = var_name
and also declaring a Var i do put - declare sID as a string??

once i have figured them 2 things out im sure i can use VBA to help me to my needs

 

[Dragonreaper]

Ok i have found out a way to do this and i am kinda hitting a problem.. lol sounds stupid but it is ha lol umm well i have gotten the vars to work so for example when the button to open a form is clicked i set it to opena form blah blah lol and i have aded the code

dim sID as string
sID = !Me[Staff ID]

Text165.Value = sID

so do you have any idea whee i go from this??
how i can retreive the var from form1 and declare it into form2 ect.. if i can do this then i can set the staff ID on form2 to sID and this should then create a new record with the staff ID for table2 as sID but i have no expreience in how i am going to get the vars from form1 to form 2 any help ??

thanks as well for your help =]

 

[neileg]

If the tables look like you describe, why have you got 2 tables? Is this a one to one relationship?

 

[Dragonreaper]

One To Many Relationship...

and no the tables are not set up like this i had to use an example because of the classified work that i am doing lol so basicly i just want table1 too add a nw record from the form i have created so say i type staff Id as 1 it will update table1.. what i want is it to update table2 and add a new record for with the same staff Id as table 1

i cant explain properly but i have gotten abit ahead for what i wanted =]

instead of using vars i have said when form2 is open when it loads it sets the textbox named staff ID to table1.[Staff ID] for example =]

kinda not working now that i added more soo say the textbox1 is called
Staff ID i put in the VBA Staff ID.Value = table1.Staff ID
that worked fine until i added more such as Name.Value = table1.Name
ect..

have any idea's?

 

[Dragonreaper]

oh i have around 50 odd tables lol.. :| not my idea but as im on work placement i had to update their database.. lol and i couldnt it was messy.. :S so i started again and just added the same tables just without the data.. and what i want is when i update table 1 it will update all the tables.. by adding new records for table2 table 3 ect..

but i found i cant do this unless i already ahve the data in table2 or table 3 ect..

so i wanted to kno if it is possible to create a new record in table2 after u create a new record in table1 with both having 3-4 feilds the same in all the tables..

you understand where im coming from now?? if i explained it better lol :S

sounds fantastic in my head lol

 

[Dragonreaper]

and where you said why have i got the 2 tables for what well basiclly the records i will be adding.. oh my lord lol there around 1000 and i thought it will be easier to just update all the tables instead of going to each one seperatly.. and adding in the different (staff ID's) to the different tables.. once i got it so the main form updates all the tables i got i will be happy lol and can finish the project.. there might be an easier way but i have enver used access until last week when my work experience began.. lol i got till friday as well to finish the database.. lol and add all around 1000 entries..
and the place where i work dont have no idea about computers let alone dataabses lol only the manager who is also looking for information for me
lol

 

[Dragonreaper]

Hmm.. in form2 i got onLoad() function

Private Sub Form_Load()

    stID.Value = Form_Main.stID
    
End Sub

works fine and how i want it but.. it displays an error after closing the popup.. lol :S

it says something about the relations.. so i took all relations off and it worked fine.. but when i delete the table1 record from the form as i added a delete record button.. it doesnt delete the table2 record.. so im looking at the relations and i jsut keep getting this error..
have any idea?

The Error Is

You cannot add or change a record because a related record is required in 'Table1'.

Dont have any clue what ive done and i got til 2morrow to fix it..

 

[Dragonreaper]

ok got It to work..

The relations where Set up Wrong Ha Lol..

But I Have hit The Second Problem.. :S

When adding a 2nd record and clicking the command button to open Form2..

it doesnt work.. it only works for the 1st record that i entered on Form1

How can i Make It Work So I Can Change record2??

here is the VBA Code Ive edited the names so it is not related to what im doing

Private Sub Form_Load()

    stID.Value = Form_Main.stID
    Title.Value = Form_Main.Title
    Surname.Value = Form_Main.Surname
    Name.Value = Form_Main.Name

End Sub

 

[Dragonreaper]

Oh My Lord.. Now i deleted record 1 from table 1 and 2 i cant get form2 to add the records.. lol.. GRRR messed this up completly =[

i hate databases lol

anybody have another approtch?

Really needa reply by tomorrow lol or i basicly fail what i ment to do for work experience lol.. oh joy =[

 

[Dragonreaper]

Hmm got another idea where i had

Private Sub Form_Load()

    stID.Value = Form_Main.stID
    Title.Value = Form_Main.Title
    Surname.Value = Form_Main.Surname
    Name.Value = Form_Main.Name

End Sub

Well maybe i can make it so it doesnt get the code from teh form but the table for example

Private Sub Form_Load()

    stID.Value = Table1.stID
    Title.Value = Table1.Title
    Surname.Value = Table1.Surname
    Name.Value = Table1.Name

End Sub

Now i tried this had no luck.. but wouldnt it make sence? it should work :S

pleasee please please help lol

ill also help some how i can if you have website problems im great at website's and php lol and funnly enough MySql :s exept its not the same for access lol

- Alan

 

[neileg]

None of this makes any sense to me. I don't understand why you want to hold the same data in different tables. I don't understand why you want to create empty records. Your business process may be confidential but without knowing what it is, I can't help.

 

[Dragonreaper]

Ok ill give a breif discription.. im not saying its exact but post of it will be..

ok so i have a database that i need to have a form that enters data into table1 this data is all the details of people name address ect.. and i have ID's that tell the subform well pop up form who the person is.. ok so th main form holds the users information then the pop up tells me other information about that person but the other information is set into other tables for example the other table is named availability and ahs colloms such as monday AM monday Pm ect..
but what i want to do on the main form i want to be able to enter the ID and this will then create a new record in the table available and add the ID to the one i put in Table1 so then when i press the available command button and that form opens it opens saying the ID is what ever is in Form1 if you under stand lol

so basicly when i enter data in form1 and then i open form2 the information passes through to form2 with the same ID name surname ect.. and then have the other information from the available table so i can change this t suit my needs instead of adding the ID name surname ect my-self

makign it easier for me to update all 1000 records by today instead of going through ever individual table/form and typing the ID Name and surname Over And Over..

- Alan

 

[Dragonreaper]

I found some Help You Recently done And told somebody About Normalisation This would be a great idea ect.. but i have to much information

and i have found this code

CurrentDb.Execute "INSERT INTO tblAthens (ParticipantID) " & _
" VALUES (" & Me.ParticipantID & ")", dbFailOnError

i and i have to enter this on after event for the record im updating..
but it seems not to be working any idea??

 

[neileg]

You don't need to duplicate the name or surname data at all. You hold the ID in the subtables and retrieve the name and surname with a query when you need them.

But you haven't explained why you want to create empty records in the subtables.

 

[Dragonreaper]

Ok So

i have got

Private Sub ID_AfterUpdate()

DBEngine(0)(0).Execute "INSERT INTO availability (ID) " & _
" VALUES (" & Me.ID & ")", dbFailOnError

End Sub

i think this works because when i open form1 it does add the ID as 1 for that table.. but when adding more such as

Private Sub Name_AfterUpdate()

DBEngine(0)(0).Execute "INSERT INTO availability (Name) " & _
" VALUES (" & Me.Name & ")", dbFailOnError

End Sub

it does not update the record to this.. is there something im doing wrong??
oh and also i got

Private Sub Form_AfterInsert()

DBEngine(0)(0).Execute "INSERT INTO availability (ID) " & _
" VALUES (" & Me.ID & ")", dbFailOnError

End Sub

maybe i have to enter the other code into this??
but i tried and it didnt work :S

Help

- Alan

 

[neileg]

Sorry but you're ignoring my fundamental advice. I'm not going to help you do something that I think is wrong.

 

[Dragonreaper]

im very sorry but as you can see i have till 4 too complete this task and im not startign from scratch.. yes it would have been more appropriate but i got 3hours and 45 minutes to finish the database... and do you really think i can do 2 weeks work in this time??

so im afraid i cant listen to the advice you give me and just carry on doing it my way as i mean im neally finished.. all i have to do now is do this VBA which i desperatly need the help on and add in the data and im done and particlly i can tell my tutor that i couldnt finish inputting the data and they would need to finish up inputting the data..

- Alan