Can’t get the SELECT DISTINCT code to work (1 Viewer)

[jdraw]

Instead of a new table, you could put the emailId that matches the bookingref into a new field in the tblBookings.
That could be done with a button click or just run the sub MatchEmails (after adjusting the writeRecord sub to update the tblBookings .

When that is completed, you could run a query involving both tables to identify EmailIds that are not in tblBookings.

At this point, you would have to parse the ImportedEmails to find some text/code that identifies a potential bookingRef. These would be new records.

What would be the format of such a search? How many characters long? Any pattern?
**This could be the hardest part of your setup**.

I guess my confusion with the thread is I do not appreciate the need for the Form.

 

[FAB1]

Instead of a new table, you could put the emailId that matches the bookingref into a new field in the tblBookings.
That could be done with a button click or just run the sub MatchEmails (after adjusting the writeRecord sub to update the tblBookings .

When that is completed, you could run a query involving both tables to identify EmailIds that are not in tblBookings.

I only require the matched values to populate the unbound fields.

At this point, you would have to parse the ImportedEmails to find some text/code that identifies a potential bookingRef. These would be new records.

I presume your now talking about “2.If no match is found I would like to know the location to put this code to start a new Booking Record in the frmBooking” Would it not just be the fact because it didn’t find a match its automatically means that the details about the booking haven’t already been added to the tblbookings.

What would be the format of such a search? How many characters long? Any pattern?
**This could be the hardest part of your setup**.

When I was referring to
Dim MsgBoxNewBooking As Integer
MsgBoxNewBooking = MsgBox("Would you like to start a new booking?", vbYesNo, "No match found")
If MsgBoxNewBooking = vbYes Then
DoCmd.OpenForm "frmBookings", acNormal, "", "", acAdd, acNormal

The frmBookings data will be inputted manually

I guess my confusion with the thread is I do not appreciate the need for the Form.

Ok

 

[jdraw]

Hmm?
I guess I'm missing something basic.

You have 2 tables. If you have a list of bookingRefs, and you have to match these with a code/text within emailbody of the second table.
Running the sub will match where there is a matching code.

If you run the Match sub to update your tblBooking, you can have a bound form to populate the controls.

I only require the matched values to populate the unbound fields.

If you run the Match sub, there should be no unbound fields/controls....as I see it.


Those emails for which you do not have bookingrefs are the crux of the issue.

What is your process to identify the bookingref in the emailbody and create a new record in tblbookings?
Is this the main purpose of the form?
-seems you have to read the emailbody
-select a code/text to be the bookingref
-the add a new record to tblbooking identifying that booking ref(folio, supplier etc) and the emailID
(but that's a separate form, or a distinct use of a form)
Is that the process?