updating form

[DajOne]

This MUST be simple

I got a form made from a table with a key field, which is the customer number [CCAN]...

Upon entering the CCAN in the field, how can I have the rest of the fields updated automatically with the data in the table?.. For the moment, it keeps the data (like address, contact name ertc..) from the previous record displayed..

I would prefer not having to do the form from a Query ...
Thanks!!

 

[Trevoke]

In the afterupdate event of the box, just refresh the form.. (I think you'll have to refresh each and every single other text box manually using fieldname.requery. Maybe .repaint will do the trick on the form, I'm not sure.) Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam.

 

[TrojanRabbit]

I'm not sure exactly what you're trying to do. I understand that you want to enter a CCAN into a box and have the rest of the form updated. If I understand correctly, then I know how you can do it. If the CCAN box is bound, it will change the CCAN of the current record. Make sure the box is unbound. In the AfterUpdate event for the box, do the following:

Me.RecordsetClone.FindFirst &quot;CCAN = '&quot; & <textbox> & &quot;'&quot;
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

If CCAN is Number type, eliminate ' in FindFirst. If this doesn't work with Me.RecordsetClone, try a recordset object. Set rst = Me.RecordsetClone Or Set rst = CurrentDb.OpenRecordset(&quot;<table>&quot.

This should work if I understand you correctly.

 

[DajOne]

Thanks TrojanRabbit...

I think that my level of VBA understanding is too low to program this..

The CCAN field needs to be bound as it updates sub-forms (with no problems). I do not know what you mean by <textbox>..

Thanks a lot for your time... It is really appreciated...
I better start learning VBA.. I feel like I can learn a whole lot here...

 

[TrojanRabbit]

<textbox> refers to the name of your CCAN textbox. I've seen this done various ways. One way is to have a bound textbox that displays the CCAN and an unbound combobox for choosing the CCAN. The textbox can be used for your subforms. The combobox is simply for selecting.

Set up the bound textbox, then a combobox. When you set up the combobox, Access will ask you what you want in it. Just select CCAN from the table. Use the AfterUpdate event for the combobox to update the form. Use the code above, and, where I put <textbox>, use the name of the combobox.

For reference, another way to set the values in a combobox is to go to its RowSource property and use a SQL statement: &quot;SELECT CCAN FROM <table name>&quot;.

Hope I didn't confuse you here. If so, let me know.

 

[DajOne]

TrojanRabbit

This is a great idea, even better than selecting the CCAN...

I created an unbound combo box with the list of the client's names...

It worked perfectly right after the modification but tan error occured when i closed the form and restarted it

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst &quot;[CCAN] = '&quot; & Me![Combo123] & &quot;'&quot;
Me.Bookmark = rs.Bookmark

The last line was highlited..

CAn it be because the form opens in a 'enter a new client' mode?... If yes, how can I change it...

Thanks for your patience!!

 

[DajOne]

I forgot,

The error is 3021... No current record...

 

[TrojanRabbit]

Yes, that could be the reason. It should only run the code after updating the combobox, though. How are you entering a new client? If you don't use the combobox to enter a new one, you shouldn't have a problem with the code in combox.AfterUpdate. If you do enter a new client using the combobox, you can use its NotInList event to add it.
One thing you can do, too, is add an If statement. This may even be easier.
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

 

[TrojanRabbit]

Actually, thinking about the error that you got, you may need an error handler. Put an On Error statement before FindFirst.

 

[DajOne]

Thanks!!

What I did is add to the form a 'new client' macro and a save button macro... IT works fine as long as the user is awake...

By not 'upgrading' the code, the unbound combo box is not updated when several clients are entered in a row... The user has to exit the form then come back..

Thanks a lot for all your help... This was a great way of doing this!!

 

[TrojanRabbit]

Oh, I see. You can use Combo123.Requery to update the combobox whenever a client is added.

 

[DajOne]

Hi Trojan,

I had to downgrade the code from Access 2000 to Access 97..
The code to update the combo box is not understood by Access 97 and I need a replacement code.. Can you help?

Private Sub Combo123_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst &quot;[CCAN] = '&quot; & Me![Combo123] & &quot;'&quot;
Me.Bookmark = rs.Bookmark
End Sub