Is it possible to automatically fill fields based on another field?

[donkey89]

Hello all.

I'm using MS Access 2007, I'm hoping somebody could shine some light on my problem.

Basically I have a form that allows order entry into the tblOrders, My database is based on the opthalmic industry, therefore I have effectively two data sets for each order, one for the Right eye and one for the left. Whilst most of the information is different, two fields in particular are often the same, which are "Right Lens Type" and "Left Lens Type".

What I would like to happen is when I enter information for the Right Lens type it automatically "copies" this information into the Left Lens type, but still allows me to change it for the rare occasion that there are lens differences.

I have tried playing around with the After update event but it appears to just Clear the Right lens field.

If there is a solution to this and I am to put it into the database what is the likelyhood of it affecting any current data I have stored? I have several hundred entries and a couple of these do have different lens types, will they be written over?

If its worth mentioning, all of my order data is stored on one table.

Thankyou in advance,

~Markus

 

[Beetle]

You could use the After Update event of the Right lens text box and trap for new records so that it only copies the value the first time you enter a new record. Example;

Private Sub txtRightLens_AfterUpdate()
 
    If Me.NewRecord Then
        Me!LeftLens = Me!RightLens
    End If
 
End Sub

 

[donkey89]

Thankyou for your assistance Beetle,

This worked perfectly, It seems so simple now.

~Markus

 

[Khalid_Afridi]

Nice user name , in a first glance it looks like a shrek's donkey... lol

 

[donkey89]

Nice user name , in a first glance it looks like a shrek's donkey... lol

Haha I thought it was a little different ,

Im going to re-use this thread, possibly should make a new one lets see how this goes.

Okay well I have added a close action to all my buttons that link to another form, so ideally only one form is open at any one time. My problem comes from a button within my customer form named "New Order" this is because it has a little VBA code attached to it as follows;

------
Private Sub NewOrder_Click()
If Not IsNull(Me.ID) Then
DoCmd.OpenForm "fmOrders", , , , , , Me.ID
Else
MsgBox "You can't create an order for a non-existant customer.."
End If

End Sub
-----

The idea is it carries over the ID number and fills it in at the other end being my order form;

------
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
DoCmd.GoToRecord , , acNewRec
Me.CustomerID = Me.OpenArgs
End If
End Sub
------

However this only works once per form "load" or so it seems, So thats why I've added closes to all forms. So i used the built in help and managed to come up with the following;

DoCmd.Close(acForm,fmCustomers,acSavePrompt)

I have placed this just under the line containing "If Not IsNull(Me.ID) Then".

As I dont want it to close if I need to then enter a ID as I'll have no forms open, but I need it to close before actioning the Open command "I think!".

Any help would be brilliant, I'm learning.. slowly

~Markus

 

[Khalid_Afridi]

Okay,

so what will happen, if you call the same code on form current event, or on form on activate event?
did you try these???

 

[donkey89]

Okay,

so what will happen, if you call the same code on form current event, or on form on activate event?
did you try these???

Hello,

I'm still new to access in general so please excuse my lack of knowledge.

I don't think I have tried the above, from what I understand do you mean switching the code from the "on load" event to another event, with respects to my Order form?

Now indirectly I have managed to get it to work as intended, mostly. The idea was to only have one form open at any one time, but that was just for a more cosmetical purpose opposed to any impact on the database. However in adding closes to all buttons that link one form to another, It now carries the ID over correctly.

Whilst if I go from the customers form -> Add new order - > Order form (with automatically filled in ID) I then have two forms open, this is okay. As if I use all the buttons I have added it will always close the Orders form before going back to the customers form.

The problem comes around if I was to manually click the currently open Customer Form whilst the Orders form is on a current order. If at this point I was to Click "Create new order" from my customers form, this would take me to the order form, but would take me to the order thats currently being viewed and not a new record as intended.

I'm sorry if this post has dragged on abit I was unsure of how to explain it/what your question was.

Thankyou!

~Markus

 

[Khalid_Afridi]

dude I am too tired right now, tomorrow will go through it and will try to understand what exactly you want
take care till than.

 

[donkey89]

dude I am too tired right now, tomorrow will go through it and will try to understand what exactly you want
take care till than.

Thankyou. Sleep well!

~Markus