Problem with default values on load (using a combo box)

[bo8482]

Hi everyone

I have a form which is bound to a table 'Client Details'.

I then have two fields on this form, which is a combo box of "client number", and a text box "client name" that populates when the combo box is updated.

I then have two subforms, which update fine when the combo box is updated.

My problem is this: when the form loads, the combo box loads up to empty field (which is OK). The problem is the text box loads up the first record in the table "Client details" and subsequently the two subforms are populated with the related records.

How can I get it so that when the form loads, it's all blank, so that the first record doesn't continually get accidentally changed/updated?

I have tried setting the default value to "", Null with no joy. Have tried searching these forums but it's very difficult to find a similar problem described.

Any help is much appreciated!! Thanks,
Brian

 

[DCrake]

In the record source property of the form enter Select * From Table Where False or Select * from Table PK field = 0. Because the PK will always be greater than 0 no records will be returned

This should give you a blank record

Then when you make a selection from the combo box reset the recordsource to apply the filter accordingly.

Select * from Table where PK = Me.ComboBox


David

 

[bo8482]

Hi David

Thanks for the reply. I'm not very advanced when it comes to SQL/Access so do I literally type what you have said in the record source box? Or do I go in SQL design view and type it in? What exactly is PK??

Thanks for your help
Brian

 

[DCrake]

Go to the properties box of the form and at the top it will say Recordsource

This is where you enter the string. PK stands for Primary Key. The unique identifier for each record.

 

[bo8482]

Ok I have typed the following in record source:

SELECT * From tblClientDetails Where False Or Select * From tblClientDetails.ClientNumber = 0

However I'm getting an error message which says "Syntax error. In query expression 'False or Select * From tblClientDetails.ClientNumber = 0'.

I played around with the expression but couldn't get rid of the syntax error. Any ideas? Thanks!

 

[DCrake]

The statements I offered were infact two seperate commands not one.

 

[bo8482]

OK apologies for being very dumb.

Have tried to get this to work without asking another stupid question but how do I get my text box to then populate with the client name selected from the combo box?

Previously as the form was bound to client details it was able to pick it up, but now it can't....

Thanks

 

[DCrake]

Assuming that column(0) holds the PK of the client and Column(1) holds the clients name then on the after update of the combo box enter

Me.Textbox = me.combobox.column(1)


David

 

[bo8482]

OK this is infruriating because I know I'm very close.

Currently it loads up a blank record, but then when I select client number form the combo box, it populates all the subforms correctly but the text box with client name in the same form doesnt work...its just blank.

The SQL behind the combo box looks like this:
SELECT tblClientDetails.ClientNumber, tblClientDetails.ClientName
FROM tblClientDetails;

The code behind my form looks like this:
Option Compare Database
Option Explicit
Private Sub Combo7_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientNumber] = " & Str(Nz(Me![Combo7], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'To update text box with same value in combo box
Me.ClientName = Me.Combo7.Column(1)
End Sub

I can't see where I'm going wrong! Thanks for your patience with this

 

[bo8482]

OK I have managed to fix this!

Basically I changed the record source to a query of all client details, then used the combo box as a parameter to drive the query. I then added IS NULL to the criteria too and it worked!

I tried doing this before but I couldn't update the query previously because it had 'TOTALS' in design mode which wouldn't let the query update the table....

Anyways, got there in the end. Thanks for your help!