Combobox

[fabrizio19]

Hi,
I :banghead:have a combobox named T in which you have two choice A and B. Now, I'm triing to do this:
When you choose A in the combobox the form should check if a record already exists.If does not exist then add a new one.If the record exists get the record that already exists.
The same thing should happen with the choice B in the combobox.
Any suggestions!
Yours
f:banghead::banghead:

 

[pr2-eugin]

Hello fabrizio19, I understand that you wish to protect your data.. but just giving A and B as sample data will not be of much help here.. What is the Data Type of the Values you are trying to compare? is it Number?? To start with research on FindFirst method after creating a record set object..

 

[fabrizio19]

thank you for your reply. I'll try toexplain it much better. The combobox is associated with two number 0 (first choice) 1 (second choice).
How can I do the findfirst method?
Yours
f

 

[fabrizio19]

can Anyone help me?

 

[pr2-eugin]

Hello fabrizio19, I am not sure how to help you with 0 and 1 as the data.. either give us some sample (actual-look-alike) data or upload a .mdb (MSAccess 2000) version of your stripped down database.. Look into this link for some pointers on FindFirst method..

 

[RainLover]

Hi,
I :banghead:have a combobox named T in which you have two choice A and B. Now, I'm triing to do this:
When you choose A in the combobox the form should check if a record already exists.If does not exist then add a new one.If the record exists get the record that already exists.
The same thing should happen with the choice B in the combobox.
Any suggestions!
Yours
f:banghead::banghead:

What you are saying is that you want only two records.

Why would you want code or even a Combo box. Just use Page Down or Up to swap from one record to the other.

 

[fabrizio19]

Dear pr2,
I have attached a light version of the db.
In the form "Anestesia" there is the famous combobox named TMA=Timing of Anestesia.The TMA can assume only two numeric value 0=induzione , 1=mantenimento.
In the header there are 3 variables that link this form to the previous form (by openargs method). Thus After that the anestesia form is opened you fill the field with the TMA combobox set on induzione (=0). After you have to pass to the second step of anestesia and the user should change the TMA from induzione to mantenimeno.(so it should add a new record). Well if the user would check the induzione from mantenimento because something has been forgotten the Anestesia form should check that the patient, the Ablazione TV and Anestesia N is the same and so should recover the record in which the timing anstesia (TMA combobox) is equal to the induzione.
Help me!
f-

 

[RainLover]

I can't find TMA.

Also your table designs are wrong. Why do they not have primary Keys.

Perhaps you should read some more on Access Design.

 

[fabrizio19]

I can't have a primiry key because same patients can have more events and so it can be duplicated.
I have change the name of TMA in Timing anestesia (header form).
Thanks
f

 

[boblarson]

I can't have a primiry key because same patients can have more events and so it can be duplicated.f

That is a statement that shows your understanding of relational databases is sorely lacking. You CAN have a primary key and you SHOULD have a primary key, even if it is a Surrogate Key like an Autonumber. And then that number is stored in the child table as a FOREIGN key, which ties the two records together. A child table can have many of the same FOREIGN keys, but each record should have its own PRIMARY key.

 

[fabrizio19]

Thank you foryour suggestions.I thought that the tables were linked by one to may relation?Is it not correct?
About the topic of combobox any solution?
Thanks
f

 

[fabrizio19]

no one can help me?

 

[nanscombe]

Private Sub TMA_AfterUpdate()

Dim strCerca As String
  strCerca = "Pts=" & Me.PTS & " And [Ablazione TV N]=" & Me.Ablazione_TV_N & " And [A_N]=" & Me.A_N & "And [Timing Anestesia]=" & Me.TMA & ""
  [B]Me.RecordsetClone.Findfirst strCerca[/B]

  [B]If Me.RecordsetClone.NoMatch[/B] Then
    DoCmd.GoToRecord , , acNewRec
    Me.A_N = (Nz(DMax("A_N", "ANESTESIA", "PTS=" & Me.PTS & "And [Ablazione TV N]=" & Me.Ablazione_TV_N), 0))

  Else
    [B]Me.Bookmark = Me.RecordsetClone.Bookmark[/B]
  End If

End Sub

You could try using Me.RecordsetClone instead of Me.Recordset for the search. It is a copy of the Form's Recordset.

The search is performed using RecordsetClone and if there is no match then your code still moves to a new record. However if a match is found the record of the form is moved to the one found in the RecordsetClone by matching their Bookmark properties.

I hope this helps.

 

[fabrizio19]

thank you for the reply!
Basicly it 'is the good direction but there still same problem.
Some time the Microsoft access gives me the erro
Method update o cancelupdate without ADDnew o Edit
Second
If you change the value in the combobox (from induzione to mantenimento) it corrctly add a new record.
But if you goback it doesn't recognize correctly the record (it doesn't match well)

f

 

[RainLover]

Why did you not take any notice of Bob Larson.

Bob is one of the better programers on this site. If you do not take any notice your Database will be Totally Unusable.

Best advice I can give is to throw away what you have and start again after learning some basics.

Sorry about that but someone needs to tell you the truth.

 

[nanscombe]

A Primary Key is a field, or group of fields, which make a record unique but which can't contain a NULL value ie it can't be empty. A key made of several fields is called a Compound (Primary / Foreign) Key.

You do appear to have these available. Here are some examples:

ID Paziente - Numero PTS, which you have already set up.

Ablazione - Numero_PTS + Data (Date in Italian I surmise) since one patient can probably only have one ablation a day?

ANESTESIA - PTS + Ablazione TV N + A_N + Timing Anestesia + Data/Ora Inizio Anestesia

Coro - PTS + Data + Timing(?) Again assuming one patient can only have one Coro event on any given day.


To make a Compound Primary Key you select more than one field in the table design page an click the Primary Key button.


All of this said it can be much easier to just create an autonumber field and use that as a link instead.

Using the path of ID Paziente -> IABP -> Complicanze Device as an example.

ID Paziente links to IABP by the fields:
Numero PTS -> PTS

IABP links to Complicanze Device by the fields:
PTS, Ablazione TV N, Inserimento IABP N -> PTS, Ablazione TV N, Inserimento IABP N

However if you had an autonumber called IAPD_Id as the Primary Key in IABP, and a number field in Complicanze Device as the Foreign Key, it would be much simpler.

ID Paziente links to IABP by the fields:
Numero PTS -> PTS

As it was before, but..

IABP links to Complicanze Device by the fields:
IABP_Id -> IABP_Id

 

[RainLover]

A Primary Key is a field, or group of fields, which make a record unique but which can't contain a NULL value ie it can't be empty. A key made of several fields is called a Compound (Primary / Foreign) Key.


All of this said it can be much easier to just create an autonumber field and use that as a link instead.

A Composite primary key although used by many is IMHO not a good idea.

The Autonumber as mentioned should be used on every table Regardless.

If you want two or more fields to be set not to allow duplicates you can use what is know as a Unique Index. A unique Index is not a Key so don't use it as one.

Nanscombe,

I will get out of your way for now.

 

[nanscombe]

A Composite primary key although used by many is IMHO not a good idea.

The Autonumber as mentioned should be used on every table Regardless.

If you want two or more fields to be set not to allow duplicates you can use what is know as a Unique Index. A unique Index is not a Key so don't use it as one.

Nanscombe,

I will get out of your way for now.

Composite Primary Key - OracleFAQs

A composite key is a primary key that consists of more than one column. Oracle will create a composite primary key index on the table to implement and enforce the composite key. Composite keys are also known as concatenated or aggregate keys.

...

CREATE TABLE line_items (
orderID NUMBER,
itemNumber NUMBER,
productId NUMBER,
quantity NUMBER,
PRIMARY KEY (orderID, itemNumber));

Personally, I used to use programatically generated keys. Autonumber was not good enough for what I need as I had over a dozen instances of database spread over England and Wales which had to be consolidated into a single datasource.

Anyway, I'll just rely on my 15+ years experience of building databases with Access, Oracle, dBase.

 

[RainLover]

Nigel

I wasn't putting you down. I was in fact endorsing what you said.

You also seem to have some experience in the OP's native language. This is a great advantage.

Please continue, you have gained much.

That's why I said I would move aside and let you continue.

 

[nanscombe]

I wasn't putting you down. I was in fact endorsing what you said.

Ok, fair enough.

You also seem to have some experience in the OP's native language. This is a great advantage...

Not really. The opening screen of his database has the word Milano on it, I'm guessing it's Milan in Italy and Babelfish is good at translation, especially one word at a time.

That's why I said I would move aside and let you continue.

When I get a bit of time later on I'll have a look under the bonnet of fabrizio's application and see if I can come up with a few suggestions to make his life a bit easier, programming wise at least.