String Variant Problem

[rio]

Hi... Need some help...
I used 2 combo box in my form (frmStnNo). I put this code at each combo box. The problem is this code for numbers string. how to change it to text string? coz [StnNo] and [SampleNo] is in text.

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

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[StnNo]=" & Str(Nz(Me![Combo4], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    Combo8.Requery
End Sub

Private Sub Combo8_AfterUpdate()

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

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SampleNo]=" & Str(Nz(Me![Combo8], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
End Sub

 

[wazz]

try
rs.FindFirst "[StnNo]= ' " & Me![Combo4] & " ' "

but i'm not exactly sure if you need a number or a string.

 

[rio]

Thank wazz for suggestion. there still got problem.
here i attach the file....View attachment TestStn1.zip

 

[wazz]

i suggest some redesigning. add a primary key to each table and make the data type 'AutoNumber', it will make your life a lot easier. you've made a basic relationship, but redo that using the autonumber field. make sure each table contains information about only ONE thing (unless you are joining two things together) . do a web search on normalization (there's tons of stuff, including the microsoft office site) and look at simple examples of how it works. the rest will fall into place after that.

another thing that will help is to rename the tables and fields. maybe you just threw this stuff in the sample and your real one has properly named tables, i don't know. but it will help to see what's going on. i think stuff is on the right track but isolate your info. i think you need a table for Stn and another for... etc. also, don't use short-form names if possible. if it is a station, use station, not stn. don't use 'MainIDNum', use StationID. tblStation will have a field called StationNumber, and so on.

 

[rio]

OK... thanks. my be i should try that way. i need to study hard and more. thanks again for ur suggestion.

 

[wazz]

http://support.microsoft.com/kb/209534

says for access 2000 but applies to all.