Opening new record and editing old record

[hayashig]

I am using MS Access 2007, and I am currently trying to implement what is already available on the "Student" template provided by microsoft.

My main goal is to use the =IIf(IsNull([ID]),"(New)","Open") field in the form to open a popup form which is used to input/edit data.

I have copied the textbox from the "Student" template and changed the embedded macro's table and form location since the naming was not the same. The field shows "open" if information already exist for that row, and "new" at the bottom where new record is recorded. The input popup form opens perfectly when I click on the "new" and records all data on the table, but when I click on the "open", a new record is made rather than showing me the already inputted record for me to edit.

If there is any way to copy/paste the embedded macro to help solve this issue please let me know.

 

[Poppa Smurf]

On the Click Event of the text box you will see [Embedded Macro] then at the right of this there are three dots, click on these dots and the embedded macro will be displayed.

 

[hayashig]

What I meant was that is there a way to make the On Click Embedded Macro in to a code to paste it onto this forum so people can see what I have done wrong?

Also, any suggestions on how to make the "open" and "new" function of the field work would be appreciated.

 

[Poppa Smurf]

1. Open the embedded macro, and conditions etc are displayed
2. In the ribbon there is Save As option, click on this and a dialog box will be displayed. Click OK and close the form.
3. In the macro group you will see Student List or the name of the macro that you just saved. Highlight the macro but do not click on it.
4. On the Main menu click on database tools, then in Macro group select convert Macros to Visual Basic. A popup will be displayed then click convert.
5. In the Modules group you will something similar to this Converted Macro- Student List this is your macro converted to VBA code.
6. Open the module by double clicking on the module and this will display the code.

 

[hayashig]

Alright I have copied the Embedded Macro


'------------------------------------------------------------
' txtOpen_Click
'
'------------------------------------------------------------
Private Sub txtOpen_Click()
On Error GoTo txtOpen_Click_Err

On Error Resume Next
If (Form.Dirty) Then
DoCmd.RunCommand acCmdSaveRecord
End If
If (MacroError.Number <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
Exit Sub
End If
On Error GoTo 0
DoCmd.OpenForm "Patient Details", acNormal, "", "", acAdd, acDialog
If (Not IsNull(ID)) Then
TempVars.Add "CurrentID", "[Study ID]"
End If
If (IsNull(ID)) Then
TempVars.Add "CurrentID", "Nz(DMax(""[Study ID]"",[Form].[RecordSource]),0)"
End If
DoCmd.Requery ""
DoCmd.SearchForRecord , "", acFirst, "[Study ID]=" & TempVars!CurrentID
TempVars.Remove "CurrentID"


txtOpen_Click_Exit:
Exit Sub

txtOpen_Click_Err:
MsgBox Error$
Resume txtOpen_Click_Exit

End Sub


The "Patient Detail" is the form that I am using to input data.
[Study ID] is the field used to specify if the row already has data that should be edited if needed indicated by "open", or if the row is empty it would be indicated by "new".

 

[hayashig]

I don't know if this might be what is causing the problem, but the #record on the form is 1 even though the number of records on the table is 565. I have set the Record Source as the Table or the Query of the table, but neither of them seems to make the number of records on the form change.

 

[vbaInet]

I'm not sure what you're trying to achieve here. You want to open a second form and its current record should be the record on the form it was opened from using an ID?

 

[Poppa Smurf]

This is your current code
On Error GoTo 0
DoCmd.OpenForm "Patient Details", acNormal, "", "", acAdd, acDialog

This is the code in the template
On Error GoTo 0
DoCmd.OpenForm "Student Details", acNormal, "", "[ID]=" & Nz(.ID, 0), , acDialog

Notice the difference in the where clause, you do not have the NZ etc.

Upload your database with some sample data so that I can help you. Also send your mdb file to my gmail address as I have problems sometimes opening zip files posted on the forum.

 

[hayashig]

I have attached a sample of the MS Access file with a few records inputted.

Currently, the problem is by clicking the "Open" or "New" only the first record is brought up with the detail inputting form.

Please ignore the "Type mismatch" since its only an issue in the sample file and not the actual file.

Also, the Form Detail form will give me an visual basic error when I don't have the [ID] field on the form1 even though I have no use for that field. I only have it to not see the error and the [ID] field is not even used in the table.

 

[Poppa Smurf]

Genki

I have used the Student template and used your table I have placed a field called ID in your table. In the attached database you will notice a table called Filters this is required by an embedded macro.

In your Study Details form you will need to ensure the field Study ID has data.

I have limited experience with macros as I prefer to write in code.

I have also sent a copy to your email account.

 

[hayashig]

Thank you for the sample and I can clearly see that it works on the sample, but I have a few questions just to implement it on my form.

1) Are all the fields on the "Filter" table necessary as some are left blank. For example, the "Sort String", "Default", and "Description" are left blank.

2) What does the "ID", "Object type", "Object name", "Filter name" and "Filter string" represent as the information listed below those fields do not seem to relate to any information on "Table1", "Study Detail" or "Study List".

3) Did you make any changes to the macro to use the "Filters" table?

I hope with this I can make this work.

 

[Poppa Smurf]

I do not know, I assume that they are something to do with Filtering the records. I did not chnage the macro in my database, as I said in my earlier post I only included the Filters to prevent the error messages displayed after using the Details form.

May I suggest that we continue this correspondence off the forum.

 

[hayashig]

Aside from the error not being there any more, the whole clicking "open" on the form opening a record and "new" opening a new record is still not working. When I click on the "Open", the first record opens, and when I click "New" the first record still opens.

It would be great to discuss this off the forum to expedite the problem solving, and thank you for persevering and being patient with my lack of MS Access knowledge.

 

[hayashig]

Poppa Smurf thank you so much for your help.

I have finally solved this problem. On the original student template, there was a field on the table called ID which I deleted since my database uses a different ID system, but this is where the problem started. By deleting the ID field which was set to autonumber, I completely messed up the ="[ID]=" & Nz(.ID, 0) Macro which Poppa Smurf pointed out to me. So, to get the "Open" and "New" to recognize the rows the records, I had to put the ID field back into the table, then have the auto number correspond to the my own ID system field. I also put back the ="[ID]=" & Nz(.ID, 0), to let the On Click macro know which row is new or already recorded.

Needless to say this was a mistake by my part to remove a tracking system of the macro. Again thank you Poppa Smurf for pointing out a vital mistake which I completely should have seen.

 

[SilEDreamer]

Deleted.

My apologies for resurrecting an old post, in which I answered my own question.

Good day...