Others have trouble seeing records in data base

[bakerri2]

I have been tasked with building a database for the company I just started working for. I've had no issues really creating the database for the most part. I have worked out most of the kinks and tweaked the forms to fit our needs over the last few days. One problem that I have not been able to fix is on the form attached.


On my computer and one of other engineers computer we are both able to pull up all the records, but the other 2 engineers in the office are only able to pull up one record when they search.

the things I have checked on this form is
1) master/ child link
2) .mdb/.accdb format
3) file location for linking files ( I put it all in one database to see if it was an issue with the table links)

I am at a loss on how to troubleshoot this issue and what my next step should be. Below is a bit of code that I have on one of the buttons for search. if you have any ideas I would be greatly appreciative.

Private Sub cmdsearchprojectnum_Click()
Dim projectvalue As String
Dim SQLstr As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qrycheckout")


Me.Combo24.RowSource = "SELECT tblAssemblyGroup.AsmGrp, tblAssemblyGroup.AsmGrpID FROM tblAssemblyGroup"
Me.Combo43.RowSource = "SELECT..........."
Me.Combo43.DefaultValue = ""
Me.Combo24.DefaultValue = ""


'projectvalue = Me.txtProjvalue.value
projectyear = Left(Me.txtProjvalue.value, 2)
projectnumber = Right(Me.txtProjvalue.value, 3)

projectvalue = projectyear & "-" & projectnumber

'MsgBox projectvalue


SQLstr = "SELECT........"


qdf.SQL = SQLstr


Me!Child21.Form.RecordSource = "qrycheckout"
Me.RecordSource = "qrycheckout"


Me.Refresh
End Sub

Thanks in advance
Richard

[June7]

Why use QueryDefs? Can just set form RecordSource property with desired sql.

But I would not change the RecordSource if avoidable. I would set the Filter and FilterOn properties of form.

Which subform are you filtering and why are you filtering subform? Isn't the subform linked to main form?

[bakerri2]

Thanks for the reply and I apologize for the time it took me to get back to your comment, sometimes vacation takes precedent over work projects. Lets see if I can answer your questions.

1) I had tried to set the recordsource property with the sql statement but it would not update the subform when a new selection was made. So the only way that I was able to get the data in the subform was to change the querydefs then requery.

2) I am filtering the lower subform. the lower subform has all the parts that we have designed in our engineering department and we need to be able to quickly search and modify information connected to that data. this was the only solution I could come up with my intermediate knowledge of vba. Us mechanical engineers aren't always meant to be coders.


Again thanks for your help

[orange]

Can you tell us about your tables and relationships?
As for your forms and controls, you might wish to name your controls with more meaningful, contextual names.
Combo24 and combo43 do not convey anything to me.
It seems ProjectNum is a concatenation of 2 digit year and some number, right?

[John_G]

It looks like you are not requerying your combo boxes after you reset the RowSource. You need to do that to update the lists:

Me.Combo24.RowSource = "SELECT tblAssemblyGroup.AsmGrp, tblAssemblyGroup.AsmGrpID FROM tblAssemblyGroup"
Me.Combo43.RowSource = "SELECT..........."
Me.Combo24.Requery
Me.Combo43.Requery

You need also to Requery the forms:

Me!Child21.Form.RecordSource = "qrycheckout"
Me.RecordSource = "qrycheckout"
Me!Child21.Form.Requery
Me.RecordSource = "qrycheckout"
me.Requery

John