Something Simple, Find and Edit.

[abrum]

Hello All,

What I am looking to do should be fairly simple, however I have been banging my head against a brick wall for a while now.

I have a table which includes an ID as well as a boolean field "X", among many others. I would like to allow the user to input an ID into a text field (possibly a combo???) and either checking or unchecking a check box to change the status of "X" from true to false or false to true. I know that the access-given find wizzard will work, but that requires an extra dialog box. Given the possible repetative nature, this would not be an ideal situation. One form/frame is what I am shooting for.

Thanks in advance for any help.

-Brum

 

[FoxProProgrammer]

Are you displaying existing records or adding a new record? The code for each instance will be slightly different. In the former case, you have to set the state of the checkbox based on the state of the boolean in the table. The user could then change it. In the latter case, the checkbox would initially be unchecked (I guess that's the best default?), and the user would input the state before saving the record. The boolean would then be saved in the table. Please post back which of these you are looking for and someone can help you with the code.

dz

 

[abrum]

It is an existing entry, only edits... no new records

 

[FoxProProgrammer]

Ok, in that case...try this:

I'd suggest a combo box instead of a text box because it makes it easier for the user to find existing records. Let's say that the name of the combo box is cboID. In the AfterUpdate event of the combo box, place the following code. This code assumes that your form is bound to the table. You can verify this by looking in the Record Source of the Form. If a table name is entered, your form is bound. If your form is unbound, please post back and I'll give you a different example.

RecordsetClone.FindFirst "ID = " & Trim(Str(cboID.Value))

If RecordsetClone.NoMatch = False
Bookmark = RecordsetClone.Bookmark
Else
MsgBox "Record not found. See Database Manager!" ' End If

You shouldn't get to the Else clause because the user selects an existing record from the combo box. If you get there, the db is corrupt or there's a problem with your code. The way this code works, is it finds the record that the user selected, and synchronizes the form to the table so the bound fields populate. I also assumed that the name of the field in the table is ID and that it is numeric. If any of these assumptions are wrong, let me know and I'll modify the code accordingly.


Best,

dz

 

[FoxProProgrammer]

By the way, the example that I gave you only finds the record. This is only half of what you were looking for. If the form is bound and the user changes the value of X, Access will automatically update the table when the user moves the record pointer or closes the form. If the field is unbound, it will take more code to change the value. If that's the case, let me know and I'll give you an example.

dz

 

[abrum]

The form is bound to a query which has only the ID and the field that needs to be updated

-Brum

 

[FoxProProgrammer]

If the form is bound to a query, then the code that I gave you won't work. You'll have to do something like this intead:

Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset&quot

rs.FindFirst "ID = " & Trim(Str(cboID.Value))

If rs.NoMatch = False Then
Form.Bookmark = rs.Bookmark ' Display the selected record.
Else
MsgBox "Record not found. See Database System Manager", 64, "Search Error"
End If

After the user changes the value of X, you would update the table with the following code. You would place this code in the OnClick event of a Save button, for example, or in the event that triggers the action to save the record.

Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset&quot

rs.FindFirst "ID = " & Trim(Str(cboID.Value)

If rs.NoMatch = False Then
rs.Edit
rs!X = ckX.Value
rs.Update
Else
MsgBox "Record not found. See Database System Manager", 64, "Search Error"
End If

 

[abrum]

For some reason I cannot dim as database, "not a user defined type." What could be causing this?

-Brum

 

[FoxProProgrammer]

The library must not be installed. From within the Visual Basic window (Not the Access window), click on Tools, References. Scroll down to Microsoft DAO 3.6 Object Library and select it. If Microsoft Access 9.0 Object Library , OLE Automation, Microsoft ActiveX Data Objects 2.1 Library, and Microsoft Forms2.0 Object Library aren't selected, you might select them too. I don't know everything that these libraries contain, but that is the most likely cause of this error. Maybe someone else can provide a summary of the contents of the libraries.

dz

 

[abrum]

Yep... I figured out that I needed the DAO as soon as I hit submit. Even after I added DAO 3.6 there were other errors (Dim Y as Database, Z as RecordSet works fine). However, there are errors with Z.FindFirst and Z.NoMatch not being in the reference catalog, I did not have "Microsoft Forms 2.0 Obj Lib." All others are either the same or newer version of those you stated. This maybe because of this line. Does this look correct?

'db as database, rs as recordset
Set rs = db.OpenRecordset("qryReworkForm", dbOpenDynaset)

I have tried this line with and without "" around tblName, which I tried both the table name and query name, to no avail.

Any Ideas?

THX -Brum

 

[FoxProProgrammer]

I am confused by the following:

Dim Y as Database, Z as RecordSet. However, there are errors with Z.FindFirst and Z.NoMatch.

'db as database, rs as recordset
Set rs = db.OpenRecordset("qryReworkForm", dbOpenDynaset)

________

If you Dim the database as Y and the recordset as Z, you would have to do this:

Set Y = CurrentDb
Set Z = Y.OpenRecordset("qryReworkForm", dbOpenDynaset)

You do need to enclose the table name in quotes like you have above.

 

[abrum]

I was a bit confusing... this is what I actually have:

********************************************************
Private Sub cboFind_AfterUpdate()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qryReworkForm", dbOpenDynaset)

rs.FindFirst "RejectID = " & Trim(Str(cboFind.Value))

If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Record not found. See Database System Manager", 64, "Search Error"
End If


End Sub
********************************************************

When the event is run it throws a compile error: Method or Data Member Not Found

-Brum