Entering info on one form and seeing it on another form... 2

[BigheadTodd]

Hello,
After spending hours looking online at various posts, I am at a loss. I ambitiously took on this project to help my local fire department. Here it goes:

I am trying work on a couple forms that share the same primary key ("Incident#"). I am typing in the data into the first form called "RunReport". I have a command button further down the page labeled "EMSCall". When I push the fancy button, it does fancy things (not that fancy, but anyway) with colors of the button, and then it opens another form "EMSCall". "RunReport" is linked to table "RequiredInfoAllCalls" and "EMSCall" is linked to table "MedicalInjury".

When that second form opens, I want to be able to see some of that data from the first page, mainly the value of the Primary Key "Incident#". Instead I get the message: "You cannot add or change a record because a related record is required in the table "RequiredInfoAllCalls". I can type in the exact same values in each field and it still gives me the dreaded message.

I am thinking it has to do with the relationships of the tables. I set up a one-to-one relationship for the tables, between the primary keys "Incident#". I also have them set up with referential integrity and tried varying the possible join types (3 of them in Access).

Does anyone have any ideas on how to fix this and eliminate the dreaded message? Any, and all, assistance will be appreciated! Thanks!

BHT

 

[RuralGuy]

Try putting:
If Me.Dirty Then Me.Dirty = False
in the code to open the EMSCall form before you open the next form and see what happens.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[Tarnish]

Hi BHT,

RG took my best guess: you have uncommitted values in your first form (read: you've typed in values but they haven't been saved to the underlying table).

I mainly posted to say that one-to-one relationships are pretty rare. I'm not saying you don't have a legitimate need for it here, just that it's rare and if you aren't sure about it being right it probably would be wise to figure that out before you go any further.

According to MS:

One-To-One Relationships
In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:
• Divide a table with many columns.
• Isolate part of a table for security reasons.
• Store data that is short-lived and could be easily deleted by simply deleting the table.
• Store information that applies only to a subset of the main table.

Just food for thought.

T

 

[BigheadTodd]

First, thank you both for replying. I really appreciate it! Some background on the project before I go further. I am trying to write a program for my fire department in which they can enter information about the calls they respond to. Effectively, I am trying to save them the expense of +$12,000 and annual renewal fees. It is my first project, so with 2 1/2 books and hundreds of websites/posts later I am sitting here.

Anyway, in response to Tarnish, I am trying to use the one-to-one relationship based on the fact the Primary Keys are the only ones that are the same in the tables. I wanted to set up this part of database/forms/program in a manner that if we respond to an 80 year-old with chest pain, we don't have to deal with the part of the report involving fires, hazmat, car accidents, etc... That was my approach in setting up specific tables. One table has data that is required for every call (addresses, dates, times). The other tables are specifically tailored to the type of call. I was trying to minimize the amount of wasted space in the table (couple that with me being a newbie and you have one heck of a project).

I am still at a loss. Now it won't let the new form open. It keeps trying to give an error code:

Private Sub Command37_Click()
On Error GoTo Err_Command37_Click


If EMSCall.Value = 0 Then
Command37.ForeColor = vbBlue
Command37.FontBold = True
EMSCall.Value = 1

Else
Command37.ForeColor = vbBlack
Command37.FontBold = False
EMSCall.Value = 0
End If

If Me.Dirty Then
Me.Dirty = False
End If

DoCmd.OpenForm "EMSCall", acNormal


Err_Command37_Click:

MsgBox "error, moron", vbOKOnly
Resume Exit_Command37_Click

Exit_Command37_Click:


End Sub


I found a posting somewhere that tells you to put it in the Error part. That didn't seem to help. I have also checked the syntax with others, and I don't see anything obvious. Do you see anything obvious? Thanks again for your time!

BHT

 

[RuralGuy]

Change the order of your code. The way you have it, you will *always* fall into the error routine. Plus, change your error routine to give you some useful indormation.

Exit_Command37_Click:
Exit Sub

Err_Command37_Click:

MsgBox "Error : " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf
Resume Exit_Command37_Click

End Sub

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[BigheadTodd]

RG,
It worked like a champ! I see how I worked myself into a corner. Thank you for your expertise!

BHT

 

[RuralGuy]

Great! Thanks for posting back with your success.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit