Urgent!! How do I fill in a form control based on data in a combo box (1 Viewer)

Gram123

Registered User.
Local time
Today, 14:53
Joined
Jan 17, 2001
Messages
13
I realise this has been answered, but I didn't undrestand the answer!
I have to program a database at work which must include lots of fields that have a code number and a name, for example, Ship Number and Ship Name.
I have a Main table containing all required fields on the form and a second table containing all possible ship names and numbers.

On the form, I have a combo box based on the Ships Table. It displays both columns, so the person entering the data can click the drop-down box and select the code if they know the ship name. I need the ship name to then enter automatically in an adjacent text box. Assuming the form is called Ships, how do I do this?

On a database programmed by someone else, they use a subform, but I've never had to use subforms, and it doesn't seem to work when I try importing it and substituting names.
Can I achieve this auto-fill without a subform?
Do I need to create a query for the Ship Name text box?

Please answer in a step-by step way, because although I work as a I.T. guy, I have learned Access without any documentation and don't understand some of the terms used!
 

BarkerD

Registered User.
Local time
Today, 14:53
Joined
Dec 1, 1999
Messages
106
Place this code in the Before Update Event of the combo box. Set the Combobox Limit to List Property to Yes

If Isnull(Me.CombControlName) = True Then
'Do nothing
Exit Sub

Else

Dim rst as DAO.Recordset
Dim db as DAO.Database
Dim strSQL as String

Set db=CurrentDB

'If code field is numeric
strSQL="SELECT * FROM [SecondTableName] WHERE [codeFieldName] = " & Me.ComboControlName & ";"

'If code field is text
strSQL="SELECT * FROM [SecondTableName] WHERE [codeFieldName] = """ & Me.ComboControlName & """;"

Set rst=db.OpenRecordset(strSQL,dbOpenSnapshot)
me.TxtBoxtoFillControlName=rst![ShipName]
'Repeat for as many fields as required.
rst.close

Set rst=Nothing
Set db=Nothing

End IF

There may be simpler ways to do this, but I am also self taught, and I know this way best.

*Chuckles*

Duane Barker
 

Gram123

Registered User.
Local time
Today, 14:53
Joined
Jan 17, 2001
Messages
13
WHAT?!?!

I don't understand SQL or whatever this is!

I urgently need a simple answer of how I can do this on a form in Microsoft Access 97, using the controls I mentioned in the first msg.

Please help!!!

Please e-mail me wiht any answers to:
graham.cat@hslltd.co.uk
 

Users who are viewing this thread

Top Bottom