Unable to add record to another realted table

L

Leo

I have a parent table (tblPt_Gen_Info) linked on one to many relation to
another table called Schd. The parent table has a field called
[Account_Number]. It is the primary key for the parent table and formatted as
LongInteger and it is an autonumber field. The table Schd has a foreign key
[Account_Number], which is formatted as LongInteger. The table Schd does
not have a primary key and does not have any field that cannot accept any
null value. The two tables are linked by fields called [Auto_Number] on one
to many. Both fields are indexed properly ,namely no duplicates on the parent
table and OK for duplicates on the table Schd. Refential integrity and
Cascade update are 'on' in the properties of the link.

Both tables have queries with the same name with all the same fields and
form the basis for the two forms that serve the user to enter the data. After
the data is entered (meaning after the patient is admitted the autonumber
generated on the parent table serves as Account_Number throughout the data
base.

I have the following code under the OnClick event on the close button on the
form so that the Account_Number will be entered into the table Schd so that
when the user opens the form (frmSchd) with link criteria the account number
will already be there along with a few selected fields from the parent table,
and the user can proceed entering the other data.

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

Dim NewData As Integer
Dim strSQL As String

strSQL = "INSERT INTO Schd(Account_Number) Values (" &
Me![Account_Number] & ");"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

MsgBox "AFTER CLOSING THIS PAGE PLEASE MAKE THE CHART"
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Problem: No error message is flashed. But there is no entry in the table
and when the user opens the frmSchd only a blank form appears and the users
have to enter the Account_Numbers that they have to write down when it is
generated on the parent form. Once they enter the Account_Number the other
few fields on the frmSchd that are brought from the parent table via the
query light up with appropriate data.

What is wrong with my code? Can somebody help.

Thanks
Leo
 
L

Leo

Sorry on my previous post the copies of the code that I stated is wrong the
OncClick event code reads like the following

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

Dim NewData As Integer
Dim strSQL As String
NewDate = Me![Account_Number].Value

strSQL = "INSERT INTO Schd(Account_Number) Values (" &
NewData] & ");"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

MsgBox "AFTER CLOSING THIS PAGE PLEASE MAKE THE CHART"
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub



Leo said:
I have a parent table (tblPt_Gen_Info) linked on one to many relation to
another table called Schd. The parent table has a field called
[Account_Number]. It is the primary key for the parent table and formatted as
LongInteger and it is an autonumber field. The table Schd has a foreign key
[Account_Number], which is formatted as LongInteger. The table Schd does
not have a primary key and does not have any field that cannot accept any
null value. The two tables are linked by fields called [Auto_Number] on one
to many. Both fields are indexed properly ,namely no duplicates on the parent
table and OK for duplicates on the table Schd. Refential integrity and
Cascade update are 'on' in the properties of the link.

Both tables have queries with the same name with all the same fields and
form the basis for the two forms that serve the user to enter the data. After
the data is entered (meaning after the patient is admitted the autonumber
generated on the parent table serves as Account_Number throughout the data
base.

I have the following code under the OnClick event on the close button on the
form so that the Account_Number will be entered into the table Schd so that
when the user opens the form (frmSchd) with link criteria the account number
will already be there along with a few selected fields from the parent table,
and the user can proceed entering the other data.

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

Dim NewData As Integer
Dim strSQL As String

strSQL = "INSERT INTO Schd(Account_Number) Values (" &
Me![Account_Number] & ");"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

MsgBox "AFTER CLOSING THIS PAGE PLEASE MAKE THE CHART"
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Problem: No error message is flashed. But there is no entry in the table
and when the user opens the frmSchd only a blank form appears and the users
have to enter the Account_Numbers that they have to write down when it is
generated on the parent form. Once they enter the Account_Number the other
few fields on the frmSchd that are brought from the parent table via the
query light up with appropriate data.

What is wrong with my code? Can somebody help.

Thanks
Leo
Click to expand...
 
L

Leo

Sorry again,

The actual code as it appears is as follows. Instead of copying and posting
as I should have I have been typing it in the post. This time it is the
actuall copy.


Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

Dim NewData As Integer
Dim strSQL As String
NewData = Me![Account_Number].Value
strSQL = "INSERT INTO Schd([Account_Number]) Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

MsgBox "AFTER CLOSING THIS PAGE PLEASE MAKE THE CHART"
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub


Thanks
Leo

Leo said:
Sorry on my previous post the copies of the code that I stated is wrong the
OncClick event code reads like the following

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

Dim NewData As Integer
Dim strSQL As String
NewDate = Me![Account_Number].Value

strSQL = "INSERT INTO Schd(Account_Number) Values (" &
NewData] & ");"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

MsgBox "AFTER CLOSING THIS PAGE PLEASE MAKE THE CHART"
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub



Leo said:
I have a parent table (tblPt_Gen_Info) linked on one to many relation to
another table called Schd. The parent table has a field called
[Account_Number]. It is the primary key for the parent table and formatted as
LongInteger and it is an autonumber field. The table Schd has a foreign key
[Account_Number], which is formatted as LongInteger. The table Schd does
not have a primary key and does not have any field that cannot accept any
null value. The two tables are linked by fields called [Auto_Number] on one
to many. Both fields are indexed properly ,namely no duplicates on the parent
table and OK for duplicates on the table Schd. Refential integrity and
Cascade update are 'on' in the properties of the link.

Both tables have queries with the same name with all the same fields and
form the basis for the two forms that serve the user to enter the data. After
the data is entered (meaning after the patient is admitted the autonumber
generated on the parent table serves as Account_Number throughout the data
base.

I have the following code under the OnClick event on the close button on the
form so that the Account_Number will be entered into the table Schd so that
when the user opens the form (frmSchd) with link criteria the account number
will already be there along with a few selected fields from the parent table,
and the user can proceed entering the other data.

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

Dim NewData As Integer
Dim strSQL As String

strSQL = "INSERT INTO Schd(Account_Number) Values (" &
Me![Account_Number] & ");"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

MsgBox "AFTER CLOSING THIS PAGE PLEASE MAKE THE CHART"
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Problem: No error message is flashed. But there is no entry in the table
and when the user opens the frmSchd only a blank form appears and the users
have to enter the Account_Numbers that they have to write down when it is
generated on the parent form. Once they enter the Account_Number the other
few fields on the frmSchd that are brought from the parent table via the
query light up with appropriate data.

What is wrong with my code? Can somebody help.

Thanks
Leo
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding New records in multiple tables 3
Selecting Fields From a Record 4
Seating chart 4
Append Crosstab Query to Existing Table 0
How to merge to tables ( 6
Crating User Form to Add Names 1
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Add additional information to excel form after original data entry 0

Top