I need help with a concept in Access (VBA)

[BukHix]

I have a form that looks something like this:

Of course, the Add button updates the table with data from the form. The Edit button prompts the user for a jobnumber and then brings in the data for that Job Number from the table to allow edits. This is all done with DLookUps.

My problem is this, I need to track everything by Job Numbers and some Job Numbers can have multiple ceiling tile (records). What is the best way to alert the user that a job has more then one tile and ask them which tile (record) to return results for?

I can do the Alert part with a DCount greater then 1 but say that the Dcount finds 3 tile number (records) for the job. Now I want to give them a list of all possible and ask them to select one. Any ideas?

 

[Alexandre]

I assume that this form is made of unbound controls, and you commit the update through the command buttons? (From what I see, it can t seem to be an association form/subform)

If Dcount(*) > 1 open a popup form containing a listbox. You can pass the JobNumber to the popup form using OpenArgs. That listbox would be based on a query gathering the information you want to show about the ceiling, and with a criteria set to JobNumber = OpenArgs.

The user selects one proposal, close the form. You grab the JobNumber of the selected row and use it back in your original form.

if you believe it makes sense and would like further development, do not hesitate to repost.

Alternatively, I believe that your form is based on two tables linked through a one (jobs) to many (ceiling tiles) relationship. You could have a combo on your form ,with a record source set to a query that would use the criteria JobNumber = Form!YourFormName!YourJobNumberControlName and gather the ceiling info you want to show to your user for them to choose. On the after update event of that combo, you would populate your form accordingly.

Alex

[This message has been edited by Alexandre (edited 10-19-2001).]

 

[BukHix]

I assume that this form is made of unbound controls, and you commit the update through the command buttons? (From what I see, it can t seem to be an association form/subform)

The form is bound to one table, would I be better off to unbind it and use recordsets to enter/edit data?

If Dcount(*) > 1 open a popup form containing a listbox. You can pass the JobNumber to the popup form using OpenArgs. That listbox would be based on a query gathering the information you want to show about the ceiling, and with a criteria set to JobNumber = OpenArgs.

The user selects one proposal, close the form. You grab the JobNumber of the selected row and use it back in your original form.

That sounds like a winning plan to me.

if you believe it makes sense and would like further development, do not hesitate to repost.

I do and I will. I will look into Access Help for OpenArgs and post back on Monday if I have more questions, thanks!

 

[Alexandre]

The form is bound to one table, would I be better off to unbind it and use recordsets to enter/edit data?

Not necessarily. It is more a matter of personnal taste. Some people prefer that approach to keep a complete control over data entry/update and for some other networking-related issues. But it requires more coding.

But this was not my point. My question came from the fact that i believe you ahve two tables and a one to many relationships underlying this form, since one job Number can match various ceiling details. In such case, if you do not choose to reflect the one to many relationship through a report/subreport association, you must certainly use unbound controls and some coding to choose which of child record(s) you want to display/work with.
If I am right, the first line of your form, related to the Job must based on one table, and the rest (Item and item details) based on another one (many side of the relationship, since their can be many items for a job?). How can it be based on one table?

Alex

[This message has been edited by Alexandre (edited 10-19-2001).]

[This message has been edited by Alexandre (edited 10-19-2001).]

 

[Carol]

Why not keep it simple, if the DCount >1, have a popup form informing the user that there are multiple records for the job selected. Have this form show the records and inform the user to double-click on the record to select which record to view.

Alternatively, if the DCount = 1, then no popup form is displayed.

 

[BukHix]

Ok being the lazy "programmer" that I am I come up with a very simple solution to this problem. If a JobNumber is found with more then one tile it will ask for the ceiling tile that the user wants to see. Anybody know off the top of their head how to make a DLookup use two criteria for the look up?

Here is the important part of what I have so far:

ElseIf DCount("[JobNumber]", "tblTileStatus", "[JobNumber]= '" & strAskNumber & "'") _
>= 1 Then
varWasFound = InputBox("More then one number was found" & vbCrLf & _
"What tile number do you want to see?", "Record Check")

enDate = DLookup("[enDate]", "tblTileStatus", "[JobNumber]= '" _
& strAskNumber & "'")

JobNumber = DLookup("[JobNumber]", "tblTileStatus", "[JobNumber]= '" _
& strAskNumber & "'")

strAskNumber is one variable in the Dlookup now I want to make varWasFound a second one. Any ideas?

 

[Alexandre]

dLookup("Field1", "Table", "Field1 = '" & CriteriaString & _
"' AND Field2= '" & CriteriaString & "'")

Take out the ' ' if your criteria variables are not of string type.

Alex

 

[BukHix]

Thanks Alexandre, I knew it would be something similar to that.

I am getting a type mismatch error now. The fields are text and I am using strings. This is what I have so far. See anything obviously wrong here?

enDate = DLookup("[enDate]", "tblTileStatus", "[JobNumber]= '" _
& strAskNumber And "[itemnumber] = '" & varWasFound & "'")

[This message has been edited by BukHix (edited 10-23-2001).]

 

[Alexandre]

Literal text must remain between quotes and variables out of quotes:
enDate = DLookup("[enDate]", "tblTileStatus", "[JobNumber]= '" _
& strAskNumber & "' And "[itemnumber] = '" & varWasFound & "'")

Alex

 

[BukHix]

Thanks for your help and bare with me a little while longer. I am getting this error:

Compile Error

Expected: List seperator or ) and it highlights the following (bolded)

enDate = DLookup("[enDate]", "tblTileStatus", "[JobNumber]= '" _
& strAskNumber & "' and "[ItemNumber] = '" & varWasFound & "'")

 

[Randomblink]

I have a question...sorry to clutter up this post, but HOW ON EARTH DID YOU DROP A PIC ON YOUR POST? There are HUNDREDS of problems that could be clarified if I could have done that with some of my forms...?
I have tried, but it always says that HTML is turned off...
If someone could help me out here I would appreciate it, that you kindly.

 

[Surjer]

Click the link that you get when you post a message:

*UBB Code Is On......

It explains it in there... I think!


[This message has been edited by Surjer (edited 10-23-2001).]

 

[BukHix]

Randomblink,

Just upload an image to the internet and then use the image tags to show them in the forum.

{IMG}URL/path to image{/IMG}

Note I used { instead of [ so that the UBB would display it correctly. If you want to see exactly what it would look like just hit edit on my post with the image and the code will be there in the message.

 

[Alexandre]

Sorry, my fault. I jumped on the first error I could see and did not check for a second one. Copy and paste disd the rest...

:
enDate = DLookup("[enDate]", "tblTileStatus", "[JobNumber]= '" _
& strAskNumber & "' and [ItemNumber] = '" & varWasFound & "'")

You were closing quotes before [ItemNumber] (litteral text: name of the field) when it had to remain between quotes.

Hope

Alex

[This message has been edited by Alexandre (edited 10-23-2001).]

 

[BukHix]

Alexandre, THANK YOU!!! It worked.