Search tables and retrieve field name

[kpfunf]

Ok, I hope I can explain this one well enough!

I have tables that contain similar data but are not all uniform; I use queries to create uniform data sets (same field names, formatting, etc.). I have a process that needs to update records in the original table. However, I cannot simply refer to a field name to update, as it can vary amongst the tables. My idea is to take a piece of data (in my example, an aircraft tail number, assigned to string variable tBadTailNumber), search the whole original table for it (tSource), and then get the field name of the record. I can then in turn assign the field name to a variable (tGetTailNumberField) to put in my Update code. Do any of you know how I would write this/ is this even a good idea?

I don't know much about searching, but I'd like to be able to use a wildcard for the field name, but that didn't work for me:

rs.FindFirst "'*' = 'N123AB'"

[kpfunf]

I got an idea at home last night and wanted to post my solution:

Sub FindTNField()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim I As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset(gSource)
For I = 0 To rs.Fields.Count - 1
Set fld = rs.Fields(I)
rs.FindFirst "[" & fld.Name & "] LIKE '" & gBadTN & "'"
If Not rs.NoMatch Then
gGetTNField = fld.Name
Exit Sub
Else
End If
Next
End Sub
 

[NeoPa]

Nice solution (own solutions are always best in a way).
Try this though (I'm assuming gSource, gGetTNField & gBadTN are controls on your form) :

Private Sub FindTNField()
  Dim rs As DAO.Recordset
 
  Set rs = CurrentDb.OpenRecordset(Me.gSource)
  Me.gGetTNField = Null
  On Error Resume Next
  Me.gGetTNField = rs.Fields(Me.gBadTN)
  Call rs.Close
End Sub

[NeoPa]

Forget last post :(

It seems I missed the point on first reading.

[NeoPa]

As this is such an inefficient process though, would you consider adding information to the original (UNION) query that the form's based on, to indicate the field name instead (and maybe even the table name too if that would help)?

[kpfunf]

NeoPa,

My question would be how do I update the original data? I posted a while ago about trying to edit data in a Union Query (learned the sad result of "you can't").

As an example of the start of this thread, most of the tables have a field "TailNumber". However, one table may have "Tail Number", another "Tail #", and another "TNumber". In the Union they are obviously all the same ("TailNumber"). But unless I go back to the original table, isn't there no other way to edit?

[NeoPa]

As far as I get what you're saying, your understanding is absolutely correct. You cannot update the data in the UNION query, or any form built upon that.

What I was suggesting is that in the UNION query, you have a field called [FName], for instance, that reflects the field name for each source table. This would then be available, pre-prepared, on your form.

I will try to give a bare-bones example to illustrate :

SELECT [TailNo], ...
FROM [Table1]
UNION ALL SELECT [Tail#], ...
FROM [Table2]
...

...would become :

SELECT [TailNo], ..., 'TailNo' AS [FName]
FROM [Table1]
UNION ALL SELECT [Tail#], ..., 'Tail#' AS [FName]
FROM [Table2]
...

Does that make more sense?