retrieve and edit record set Form issue

[Mina Garas Daniel]

Dears
I am not experts in vba codes like most of you
so i need help in below issue

i need to create a form to perform the following

1- retrieve record set using ID from table A
2- edit this records
3- save edited record set to Table A with new ID

I know its will need queries or vba codes
but i try so many times and search for many times but failed to finish this

your kindly help appreciated

thanks

 

[mike60smart]

Hi

Can you upload a zipped copy of your database?

 

[theDBguy]

Hi. We'll need more information. Are you trying to duplicate a record or multiple records?

 

[vba_php]

i need to create a form to perform the following

1- retrieve record set using ID from table A
2- edit this records
3- save edited record set to Table A with new ID

you don't need code to do that:

1) make form. recordsource = "select * from tableA" (make sure the field "ID" is a long integer (not an autonumber) and duplicates are NOT allowed.
2) user edits records directly in the form controls that are bound to the table fields (they change the ID to whatever they want). if they change the ID number to one that is already in tableA in a previous record, they will get a built-in access error.
3) when the user closes the form or navigates to a new record, the record they edited is saved automatically.

there are plenty of other ways to do this, and if you want more control over the user, you will prolly have to use a bit of code.

 

[Mina Garas Daniel]

Dears
I'm trying to upload access file but its invalid !!!

Unfortunately id is auto number because its in relationship with another table
how i can do it without change autonumber

 

[theDBguy]

Dears
I'm trying to upload access file but its invalid !!!

Unfortunately id is auto number because its in relationship with another table
how i can do it without change autonumber

Try to zip it up first.

 

[Gasman]

Perhaps think of it another way

1- retrieve record set using ID from table A
2- Create new record set to Table A with new ID
3- edit this new record

Just a thought?

 

[vba_php]

how i can do it without change autonumber

put a button on your form and put this code behind it:

Dim field1 As string 'change the data type based on what type the field is
Dim field2 As string
'repeat above lines until you have the same number variables declared that are as many fields in your form (do not include the autonumber field)

    If Me.Dirty Then
        field1 = Me.nameOfField1ControlHere
        field2 = Me.nameOfField2ControlHere
        'repeat above for every field except autonumber

        Me.nameOfField1ControlHere = Me.nameOfField1ControlHere .OldValue
        'Me.nameOfField2ControlHere = Me.nameOfField2ControlHere .OldValue
        'etc, etc....

        DoCmd.Save
        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
        
        Me.nameOfField1ControlHere = field1 
        Me.nameOfField2ControlHere = field2 
        'etc, etc....

    End If

there are a million ways to do this, but this is prolly the easiest way for a non-programmer.

 

[Mina Garas Daniel]

Perhaps think of it another way

1- retrieve record set using ID from table A
2- Create new record set to Table A with new ID
3- edit this new record

Just a thought?

how can i do step No.2 after did No.1

 

[theDBguy]

how can i do step No.2 after did No.1

Is ID an Autonumber field? If so, Access will automatically create it for you. How are you adding new records now? I have a funny feeling you're trying to "duplicate" an existing record. Is that the case?

 

[Gasman]

how can i do step No.2 after did No.1

Use an append query, then go to the that record.?

 

[Mina Garas Daniel]

Is ID an Autonumber field? If so, Access will automatically create it for you. How are you adding new records now? I have a funny feeling you're trying to "duplicate" an existing record. Is that the case?

No i don't duplicate records
i enter records as for first time like

First name
second name
address

if address has been change
i need to retrieve 3 records then change address but with same first and last name and add them to a tableA

so in this case no duplicated records

as example
ID : 1
first name : mina
last name : garas
address : Africa

in case address will change

i need to change it by retrieve old data in ID 1 and edit it and add them again as new record set in table A with new ID

then

ID 2
first name : mina
last name : garas
address : Asia

Thats all
Thanks

 

[vba_php]

Mina,

I'm not sure if you can use this, but it's like what I posted earlier...

 

[Mina Garas Daniel]

Mina,

I'm not sure if you can use this, but it's like what I posted earlier...

Thanks alot for your support
but there a problem
attached file .mdb i use .accdb
so its not working also because unknown publisher

Thanks

 

[vba_php]

attached file .mdb i use .accdb
so its not working also because unknown publisher
Thanks

https://www.experts-exchange.com/questions/22863423/MS-Access-Unknown-publisher.html

 

[Mina Garas Daniel]

https://www.experts-exchange.com/questions/22863423/MS-Access-Unknown-publisher.html

I don't know what i say
i am shy from you
its not working also
no tables or quires or forms ass attached file

 

[vba_php]

I don't know what i say
i am shy from you
its not working also
no tables or quires or forms ass attached file

press the F11 key.

 

[Mina Garas Daniel]

press the F11 key.

thanks alot
thats what i need

one more question please
is this can work with tables in one to many relationships

thanks

 

[vba_php]

is this can work with tables in one to many relationships

depends on the source u use for ur form. if you continue to do what ur doing and ur forms' necessities become more complex as time goes on, ull find that you don't want to use this method anymore cuz it'll become un necessarily cumbersome. but in general, people query data that is linked by one-to-many, and the query objects can then be used as sources for ur forms. that will allow you to do what ur doing still with minimal difficulty, cuz auto-forms and stuff that are created thru the form wizard, for example, automatically link ur main form with any subforms that are automatically created. thus, doing the same thing in that scenario would be almost as easy as what ur doing now on the single form.

as far as u being shy about me, why in the world is that? cuz I seem knowledgeable about this? don't be afraid to ask things here. i'm hardly the smartest guy around here. there are many people looking at ur post that have me beat in terms of experience and expertise in access.