Easy ? how to save current record when a combo box has lost focus

[lrAsIgo]

What command should/could I use to save the current record after a combobox has lost focus or after update.

Basically, I need know if the selection chosen in the combo box is already been assigned to another record in the same set of records (no duplicate assignments).

Thanks.

 

[David R]

Try the BeforeUpdate event

Since you are (presumably) wanting to avoid doing this, not just know about it when it happens.

If Dcount("[FieldBehindCombo],"[TableName]","[FieldBehindCombo] = '" & Me.ComboBoxName & _
 "' AND [FieldThatIdentifiesThisSetOfRecords] = " & Me.ControlThatShowsThatValue Then
   MsgBox "You cannot duplicate this selection. Please choose another."
   Cancel = True
End If

Note that this assumes the combo box is text and the other field is numeric. Watch the single quotes around the text values.

You said something about "already been assigned in the same of records". I took that to mean there's some secondary identifier, like you are only checking that it hasn't been duplicated for this Division, or this week, or whatever. If you're checking your entire dataset then only the one condition is required.

 

[lrAsIgo]

In this case...

I have 3 forms built from 3 tables, each table has a field which requires a unique entry which is a FK from a forth table.

The combo box or combo boxes (3, 1 in each form ) in question is created from this forth table.

Each combo box in each form allows the user to select from a list of text values which is a PK field in the forth table. This selection is then used as a unique FK in each of the form/tables.

My first post was to solve the problem of duplicate values in one table. Then I could do the same thing for the other two ( I think).

When I use your code I get an error... expected list seperator or )

debugger highlights [ComputerData] part.

If Dcount ("[DataPanel],"[ComputerData]","[DataPanel] = '" & Me.cmbDataPanel & _
"' AND [DataPanel] = " & Me.DataPanel Then
MsgBox "You cannot duplicate this selection. Please choose another."
Cancel = True

any ideas?

 

[David R]

When I use your code I get an error... expected list seperator or )

debugger highlights [ComputerData] part.

If Dcount ("[DataPanel],"[ComputerData]","[DataPanel] = '" & Me.cmbDataPanel & _
"' AND [DataPanel] = " & Me.DataPanel Then
MsgBox "You cannot duplicate this selection. Please choose another."
Cancel = True

any ideas?

Yep - you missed a closing quote on the [DataPanel] part.

I'm not sure I understand what you're trying to do, so let me restate it as I see it.

You have a table, Table4, which is your 'main' table. It has a PK field, as any good table should, which happens to be Text. Then you have Table1, table2, and Table3, each of which relates to table4 by way of the PK, which is now a FK in their table. So far so good..

So what you're trying to do is make sure that any one FK entry is not duplicated in any one of those three tables? Your Dcount has both the combo cmbDataPanel, presumably looking up in Table4 for its values, and a text field DataPanel. How does the latter differ from the former, please?

As I've described it it sounds like a One-to-One relationship between Table4 and each of the other three tables. Which is rather unusual, and makes me think I've missed something.

Perhaps we can back up and, without using forms and controls, explain what you're trying to do. What do your four tables represent, briefly? What links them?

 

[lrAsIgo]

If I understand your question correctly, How does the latter differ from the former? Then... the latter (text field DataPanel) is a field which exist in all of the first three tables i.e.table1, 2 & 3. The former combo cmbDataPanel, which by the way your presumption is correct, looks up the values in table4, a text field which is the PK and is also named DataPanel.

In otherwords each table has a field called DataPanel.

Table1, table2, table3 each have a field with the name DataPanel which are FK's and Table4 also has a field named DataPanel which is a PK.

Briefly, Table1 contains ComputerData Records, Table2 contains PrinterData Records, , Table3 contains PeripheralData Records, and table4 contains DataPanel data which corresponds to a matrix for the precise location of each physical componenet connected to a WAN.

What links them? A 5th table which contains barcode data. Each record in each table is assigned a barcode. This is a one to one relationship. The barcode is the PK in each table.

The Datapanel (table4) is related in the same way except for the fact that the DataPanel field in the Table1,2 &3 is a FK. However it is a one to one relationship.

Do you have a thought on the underlying structure/design?
This is designed for an Assest MGMT DB.

 

[David R]

If Dcount ("[DataPanel][b]"[/b],"[ComputerData]","[DataPanel] = '" & Me.cmbDataPanel & "' AND [DataPanel] = " & Me.DataPanel Then
   MsgBox "You cannot duplicate this selection. Please choose another."
   Cancel = True

The missing quote was at the end of [DataPanel]. The underscore is just a way to represent long lines in VBA code with a line break. You can take it out and use it as above with no problem, it just may make the line longer than your screen.