Allocate a record to a place in a table

[Graham63]

Hi Guys,

I've got a table [Devices] with some number fields that are already in there for another purpose.

In a different exercise I will manually collect more information from a layout drawing and give each piece of information I collect a unique reference(this could be as simple as a PK ref) and attach a "type" and "location" . This will be done randomly in a batch exercise any time.

I need to put the above into access and hold it until I choose to individually allocate these records to the number field that already exists in the [Devices] table.
I would like to add "say" a combo/List box in [Devices] where I could search for the PK Ref which when selected against a number would populate the [Devices] table with previously unallocated information. I would like to keep any combo/List box down to unallocated PK refs to make records easy to find. I guess this a kind of cut and paste exercise.
Now I'm no expert but it seems to me it would be easy to create a situation that might bend the rules of normalization. Before I go ahead I would very much value your advice.
Many Thanks

 

[omnibaer]

Well, that sounds like an adventure. Have you ever used DAO Recordsets before?

For example:
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("SELECT * FROM Devices", dbOpenDynaset, dbSeeChanges)
If RS.Recordcount > 0 Then
RS.FindFirst "ID = " + <whatever ID you want>
If RS.NoMatch Then
<error>
Else
'populate fields
RS("field 1") = <data>
RS("field 2") = <data>
End If
End If
If Not RS Is Nothing Then RS.Close
Set RS = Nothing

Just an example; feel free to tailor to your needs. I use these religiously when complex data operations, especially involving multiple complex and/or dependent queries are involved.

 

[Graham63]

Hi, and thanks very much for your reply, I'm not too familiar with the concept of recordsets but I can read through some of the lines of code that you have used. I teach myself and know a little of most of the concepts, I do find Access and VBA fascinating, could you walk me through your code so I can work out if I have explained myself correctly and so I can fill in any gaps in my knowledge.

 

[jdraw]

Recordset tutorial from FunctionX
Good luck

 

[omnibaer]

Great tutorial, very detailed.

My notes from the code I posted:
- The RS.Recordcount check is important to ensure the recordset opened properly and that there are actually results to search through.
- The RS.NoMatch check gives you the return from the RS.FindFirst call. If there is a match, the recordset is already pointed to that record; if not, NoMatch is set to True.
- I always check to make sure the recordset exists before closing, hence:
If Not RS Is Nothing Then RS.Close
Otherwise, if the OpenRecordset() call fails and error trapping either doesn't catch it, or you want to close recordsets post-error, this ensures you don't get unhandled exceptions.

Anything in <> is for you to fill in, as well as the "field 1" items, which are the actual names of those fields. Alternatively, you can reference data by using Recordset.Fields(field name).Value or Recordset.Fields(Index).Value. I use indexed fields when I'm writing code for no table in particular; that way, you can iterate through all the fields in the collection (For i = 0 to RS.Fields.Count) and work your magic from there.

If you give some more specifics, I can help you tailor all this a bit more too.

 

[Graham63]

Ive read the Recordset tutorial and I'm going to use your example to update my Devices Table, I will probably get stuck but I like to persist because I get the experience. I imagine learning all this to be like learning to speak mandarin! Thank you very much for your posts I may need the help you offered to tailor the code, thanks again.