If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need Help With Auto-Population Access Form from Input Box
I need help with getting one of my forms to auto populate if a text string is
found in the table. The Database is used for medical record tracking of documentation. The key record is the SSAN (social security number). I have a form based on a table called TblRecords and a subform in the form based on a table called TblPtInfo. TblPtInfo is the unique patient information and contains no duplicate information What I need to happen is for the subform to update automatically if a SSAN is typed in and is found in the table. If it is not found then I will have the form use the add new record. Right now I cannot get it to work. this is what I have used: Private Sub Text14_AfterUpdate() Dim InpSSAN As String Dim rsSSAN As DAO.Recordset ' Capture item code entered InpSSAN = Me.Text14 ' Fetch item record from Item Master table Set rsSSAN = CurrentDb.OpenRecordset("SELECT * FROM TblPtInfo WHERE SSAN = " & InpSSAN) ' Check item was found If rsSSAN.EOF Then MsgBox "New SSAN", vbCritical, "Error" rsSSAN.Close Set rsSSAN = Nothing Exit Sub End If ' Item found, copy data Me.ID = rsSSAN!ID Me.FirstName = rsSSAN!FirstName Me.LastName = rsSSAN!LastName Me.SSAN = rsSSAN!SSAN Me.Admission_Date = rsSSAN!Admission_Date Me.D_C_Date = rsSSAN!D_C_Date Me.Ward = rsSSAN!Ward ' Finished with recordset rsSSAN.Close Set rsSSAN = Nothing End Sub This does not work as it always gives me the "New SSAN" error even on a known SSAN. I would also like to tie it into the main record to update the SSAN field on that form. Please help if you can. I am new at this and tinkering with other code as I learn what it is doing. |
#2
|
|||
|
|||
Need Help With Auto-Population Access Form from Input Box
Your SSN field is a text field so you need quotes. Here's a pretty good
explaination on the syntax: http://www.mvps.org/access/general/gen0018.htm Having said that, normalization rules demand that the data *only* exist in one table in your system. All you should need in other tables is the PrimaryKey as a ForeignKey. Then use a join in a query to access all of the data in the two tables. Post back here if you need further details. AlvinChadwick wrote: I need help with getting one of my forms to auto populate if a text string is found in the table. The Database is used for medical record tracking of documentation. The key record is the SSAN (social security number). I have a form based on a table called TblRecords and a subform in the form based on a table called TblPtInfo. TblPtInfo is the unique patient information and contains no duplicate information What I need to happen is for the subform to update automatically if a SSAN is typed in and is found in the table. If it is not found then I will have the form use the add new record. Right now I cannot get it to work. this is what I have used: Private Sub Text14_AfterUpdate() Dim InpSSAN As String Dim rsSSAN As DAO.Recordset ' Capture item code entered InpSSAN = Me.Text14 ' Fetch item record from Item Master table Set rsSSAN = CurrentDb.OpenRecordset("SELECT * FROM TblPtInfo WHERE SSAN = " & InpSSAN) ' Check item was found If rsSSAN.EOF Then MsgBox "New SSAN", vbCritical, "Error" rsSSAN.Close Set rsSSAN = Nothing Exit Sub End If ' Item found, copy data Me.ID = rsSSAN!ID Me.FirstName = rsSSAN!FirstName Me.LastName = rsSSAN!LastName Me.SSAN = rsSSAN!SSAN Me.Admission_Date = rsSSAN!Admission_Date Me.D_C_Date = rsSSAN!D_C_Date Me.Ward = rsSSAN!Ward ' Finished with recordset rsSSAN.Close Set rsSSAN = Nothing End Sub This does not work as it always gives me the "New SSAN" error even on a known SSAN. I would also like to tie it into the main record to update the SSAN field on that form. Please help if you can. I am new at this and tinkering with other code as I learn what it is doing. -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200805/1 |
Thread Tools | |
Display Modes | |
|
|