Please Help Help Help

[rajput]

Sorry I am posting again since I am still stuck, but now I am posting it with an example. This might help what I want to accomplish.

ALSO People have suggested me to make two tables and have one-to-many relationship, however the data is allready inputed, and it will be difficult to arrange all the database again.

Please only post if you can help me with exactally what I want to do. Thankyou



HERE GOES THE EXAMPLE:

Lets take for an instant that we are issuing visas to people holding passports. Now lets take for example Mr. John Doe comes and gets a 6 months visa, we enter all of his info. like LastName, FirstName, DateOfBirth, PassportNo., PassportDateOfIssue, and so on the first time.

Now he comes back again to get visa (since his old visa expired) after lets see a year and again we have to input all of his info. again like LastName, FirstName, DateOfBirth and so on.

Now what I want is, as soon as we enter his passport no. and computer see that he exists in the table and all of his previous information gets copied to a new record with the passport no. as well but leaving the some fields empty, so that we can enter a new data on them.

So in this passport number has to be duplicate.

I know I can pull up the old record with a combo box but then I am changing his old record but I want to keep that record as it is without changing anything on it.

And if the applicant is not in the table then we just enter all info. like Enter Last Name then First Name and then so on.

And this is in one table only.

Any help with coding will be appricated.


So I can just get away by just copying the record that will do.

Thank you

Rajput

 

[llkhoutx]

Your database seems to be poorly designed.

Passport Number, Names, identifying information should be in one table (tbPassport). Action (tbVisa) should be in another related to tbPassport, one (tbPassport) to many (tbMany).

One of the cardinal premises of relational databases is to have data only in one place.

The course you're on is a recipe for disaster.

 

[kbrooks]

I can't fix your original problem but I have a suggestion for splitting out your database into several. Copy your table and rename the copy, then take out fields in each table that don't belong there. And then of course you have to set up relationships, etc.

Maybe I'm way off....this is just off the top of my head.

 

[MarionD]

Hi Rajput,
Although I agree with Ilk about the general design I know it's not always easy to change things so quickly. I do suggest you consider it but to solve you immediate problem I would go about it like this:

On the after update of the passprt nr put in the procedure:
Dim db as database, recs as recordset
Set db= currentdb
If dcount(“*”,”Tablename”;”[passportnr]=” & me.passportnr) > 0 then ‘ this means there is record
Set recs=db.openrecordset(“Select * from tablename where [passportnr]= & me.passportnr”)
Recs.movefirst
Me.[Surname]=recs![surname]
Me.[firstname]=recs![firstname]
‘etc. until you have filled out the fields you need
recs.close
set recs=nothing
end if

This will just fill out the names from the first record with the same passport nr. ( I gather it doesn’t matter which record is selected as the Name etc. stays the same)

PS : I HAVE NOT TESTED THIS CODE.