Combo Row Source List Problem 1

[anujkmehrotra]

Hi,

I have to update a combo Row source list. My Structure is as below:

tblModelService:
ModelNo = Alba (PK)
Service = First;Second;Third

tblJobcard:
JobCard_No = PrimaryKey
ModelID = Alba
cboService = ?
ChasisNo = UniqueCode

Problem#1

Now I tried to update row souce of cboService in tblJobcard as per tblModelService!Service but failed.

Problem#2

After Row Source update:

if tblJobcard has data of that particular Service as per ChasisNo then it should not update in Rowsource of cboService.

Now please tell me how to solve it????

I'm using Access2003

Thanks & Regards,
Anuj

 

[MazeWorX]

so if im understanding you

cboService rowsource should be > SELECT tblModelService.* FROM tblModelService;

Then Column Count = 2
Column Width = 0;1 << which displays only the description
Bound column should be 1 << This is the value written to the table(PK)

Thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[TheAceMan1]

How are ya anujkmehrotra . . .

Your post origination is very vague ... to say the least. Thats why its had a tendency to fall thru until now. [blue]Problem#1[/blue] makes sense but ...

[blue]Problem#2

After Row Source update:

if tblJobcard has data of that particular Service as per ChasisNo then it should not update in Rowsource of cboService..[/blue]

... throws a curve ... [purple]be more specific in detail about this![/purple] I'm looking for a road ... and only you can provide that ... Note: [blue]MazeWorX[/blue] is making a best guess!

[blue]Your Thoughts? . . .[/blue]

Post the method by which you attempted to [blue]update[/blue] the combobox ... not in words ... but the actual code! ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

yep absolutely correct Ace I have to admit the second request has me a little confused

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[anujkmehrotra]

Hi all,

Thanks for your replies, but i share my problem once again:

I have two tables:
table#1= tblModelService
Fld#1= ModelNo = Alba (SampleData)
Fld#1= Service = First;Second;Third (SampleData)

table#2= tblJobcard
Fld#1= JobCard_No = 1 (SampleData)
Fld#2= ModelID = Alba (SampleData)
Fld#3= cboService = First (SampleData)
Fld#4= ChasisNo= ABC123 (SampleData)

Now in FrmJobcard.ModelID & ChasisNo values come automatically as default value from FrmCustomers in new record.

1# But what I want in FrmJobcard.cboService is that it Row Source List should come from tblModelService.Service (First) as per FrmJobcard.ModelID (Alba).

2# if FrmJobcard.ChasisNo (ABC123) has already value of such Service (First) then cboService Row Source List should not show that value (First), it should show the next values like- Second;Third;etc.

I hope this description will help you to understand my problem.





Thanks & Regards,
Anuj

 

[TheAceMan1]

anujkmehrotra . . .

Provide an example ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

First you should normalize all of your data. The code assumes there is a table called tblServiceStatus to store the 'First, Second, etc with a primary key numbered 1 to ? It is untested and you may need to adjust the SQL to suit but it will give you the general idea. Its not pretty but should work

Private Sub Form_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strfilter As String
    Dim intModelService As Integer

    strSQL = "SELECT tblModelService.*, tblModelService.ModelNo " _
           & "FROM tblModelService " _
           & "WHERE (((tblModelService.ModelNo)=" & [Forms]![FrmJobcard].[ModelID] & "));"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)

    intModelService = rs!Service

    If intModelService = 1 Then
        strSQL = "SELECT tblServiceStatus.*, tblServiceStatus.ServiceID " _
               & "FROM tblServiceStatus " _
               & "WHERE (((tblServiceStatus.ServiceID) <> 1));"
    Else
        Do Until intModelService = 0
            strfilter = strfilter & "(((tblServiceStatus.ServiceID)<>" & intModelService & ")) OR "
            intModelService = intModelService - 1
        Loop

        strfilter = Left(strfilter, Len(strfilter) - 4)

        strSQL = "SELECT tblServiceStatus.*, tblServiceStatus.ServiceID " _
               & "FROM tblServiceStatus " _
               & "WHERE " & strfilter
    End If
    Me.cboService.RecordSource = strSQL
    Me.cboService.Requery
End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

one other thing you'd have to test for the last record on the status table

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

Was a bit quick hitting the submit button ... sorry i should of mentioned. I don't know how you are populating your forms or in what order so the event that triggers the code may be better suited to the afterupdate event of another control which you then need to adjust the code accordingly. The tblServiceStatus is as follows

StatusID - Number - PK
ServiceStatus - Text - eg 'First, second etc

The tblModelService should now have the StatusID written to the ServiceId field and as mentioned above you need to see if your at the last record on the status table which can easily be accomplished by comparing intModelService to the Status table

Thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

So I assume you didnt like the solution provided?

http://www.tek-tips.com/viewthread.cfm?qid=1678183

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[anujkmehrotra]

Thanks MazeWorX,

I'm new in VBA, but I could not understand tblServiceStatus process. I don't have such table and you did not mention to create it.

So, I tried alternate:

http://www.tek-tips.com/viewthread.cfm?qid=1678183

But, this is not the solution what i'm looking for.

If you help me to understand what is your code then i it will be an ease to use it.

Thanks & Regards,
Anuj

Thanks & Regards,
Anuj

 

[MazeWorX]

Yes this is a NEW table it normalizes your data
tblServiceStatus holds the values, First Second etc. It has a primary key called ServiceStatusID. You match the ID with the service like

ID 1 Service: First
ID 2 Service: Second

The idea of the code is to detect the existing service ID then build the Where clause by using <>(Not) So as an example if the Service level is Second it loops from 2 to 0 building the Where so it eliminates the values 1 and 2 from the combo and only displays what values are left in the table. I have added an if statement at the beginning to check for the last entry in the status table other wise you get an empty combo.

Private Sub Form_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strfilter As String
    Dim intModelService As Integer
    Dim n As Integer

    Set db = CurrentDb()

    strSQL = "SELECT tblServiceStatus.* " _
           & "FROM tblServiceStatus;" _

    Set rs = db.OpenRecordset(strSQL)
    n = rs.RecordCount

    strSQL = "SELECT tblModelService.*, tblModelService.ModelNo " _
           & "FROM tblModelService " _
           & "WHERE (((tblModelService.ModelNo)=" & [Forms]![FrmJobcard].[ModelID] & "));"
    Set rs = db.OpenRecordset(strSQL)

    intModelService = rs!Service

    If intModelService = n Then
        'This is the last service in the table so we populate it with it only
        strSQL = "SELECT tblServiceStatus.*, tblServiceStatus.ServiceID " _
               & "FROM tblServiceStatus " _
               & "WHERE (((tblServiceStatus.ServiceID) =" & intModelService & "));"
    ElseIf intModelService = 1 Then
        'Current service is First so remove it from the list
        strSQL = "SELECT tblServiceStatus.*, tblServiceStatus.ServiceID " _
               & "FROM tblServiceStatus " _
               & "WHERE (((tblServiceStatus.ServiceID) <> 1));"
    Else
        'Remove all previous Service from the list
        Do Until intModelService = 0
            strfilter = strfilter & "(((tblServiceStatus.ServiceID)<>" & intModelService & ")) OR "
            intModelService = intModelService - 1
        Loop
        'Trim the extra OR from the strfilter
        strfilter = Left(strfilter, Len(strfilter) - 4)
        'build our SQL
        strSQL = "SELECT tblServiceStatus.*, tblServiceStatus.ServiceID " _
               & "FROM tblServiceStatus " _
               & "WHERE " & strfilter
    End If
    Me.cboService.RecordSource = strSQL
    Me.cboService.Requery
End Sub

This code is untested and as stated above should probably be attached to an AfterUpdate Event of the (Im guessing) FrmJobcard.ModelID control. You will have to decide for yourself what works best for you. The SQL may also have to be adjusted for your control names etc.


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[anujkmehrotra]

Not working dear. it returns an code error on Form open:

"Method or data member not found"

and focus goes to

Me.cboService.RecordSource = strSQL

Please guide.

Thanks & Regards,
Anuj

 

[MazeWorX]

Sorry it should be RowSource you may want to change the event?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[anujkmehrotra]

Thanks for your kind response but it is still not working on any event at all. Same error is coming.


Thanks & Regards,
Anuj

 

[MazeWorX]

I have simplified the code and highlighted in blue items that you need to change to reference your control names. It has also been tested on this event and functions fine

Private Sub Form_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim intModelService As Integer
    Dim n As Integer
    
    Set db = CurrentDb()

    strSQL = "SELECT tblServiceStatus.* " _
           & "FROM tblServiceStatus;" _

    Set rs = db.OpenRecordset(strSQL)
    rs.MoveLast
    rs.MoveFirst
    n = rs.RecordCount
    strSQL = "SELECT tblModelService.*, tblModelService.ModelNo " _
           & "FROM tblModelService " _
           & "WHERE (((tblModelService.ModelNo)=[b][blue]'Alba'[/blue][/b]));"
    Set rs = db.OpenRecordset(strSQL)

    intModelService = rs!Service

    If intModelService = n Then
        'This is the last service in the table so we populate it with it only
        strSQL = "SELECT tblServiceStatus.*, tblServiceStatus.ServiceID " _
               & "FROM tblServiceStatus " _
               & "WHERE (((tblServiceStatus.ServiceID) =" & intModelService & "));"
    Else
        strSQL = "SELECT tblServiceStatus.*, tblServiceStatus.ServiceID " _
               & "FROM tblServiceStatus " _
               & "WHERE (((tblServiceStatus.ServiceID)>" & intModelService & "));"
    End If
    [b][blue]Me.Combo0[/blue][/b].RowSource = strSQL
    [b][blue]Me.Combo0[/blue][/b].Requery
End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PL01]

...you guys are talking in Hebrew. VBA is written in English. It's not an App...its Access Application....

 

[MazeWorX]

PL01 unless you have some input of value keep it to yourself

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[anujkmehrotra]

Thanks a lot MazeWorX.