Continuous form going horizontal

[chrisjames25]

Hi. Basically i am fed up with the speed and reliability of some software my company paid too much for and i am trying to build a database to replace it and so far so good in most of the forms and rpeorts needed for the office end of the system.

THe one trickier part which i am turning my attention to now is the end that allows workers to choose what mix of plants they are picking up and for which customer.

First issue is i want to keep the look and feel similar because it actually works and is liked. SO my question is this:

Is it possible to create a continuous form that fills down to the bottom of the form height and then once hits the bottom rather than adding a scroll bar and allowing you to scroll further it creates a second column of continuous form and so on until all the varities in the query/table are listed on the form.

I haver attached an image of existing form so you can have a look and see what i mean.

 

[Ranman256]

forms only show data vertical, not horizontal.
but,
REPORTS can do this,
set the # columns,
then scroll up/down then next col
or
left/right, then next row.

 

[CJ_London]

or you can use a number of subforms set side by side

 

[static]

or a browser

View attachment moviedate.accdb

 

[CJ_London]

@static - neat

 

[chrisjames25]

THnaks for replies guys.

To extend further on my issue. What basically happens in the image i attached is that someone will click on the name of the particular plant and it will then enter a predefined number in the column next to it. And each time you click on another one it enters a number relating to that variety. You can also manually enter a number next to a variety if it isnt the predefined number you require.

This info is then saved in a temp table and when i say send to invoice the vlaues greater than 0 get sent ot the invoice.

So i see that i have 2 options form what you are saying:

1) Do subforms side by side with a prescrived height and a prescribed number of continuous forms entries that can fit in that height and then multiple it to the left as many times as i think the maximum is.

2) ALternatively do a similar thing but literally on main form enter as many textboxes as i think would ever be populated by the variety list and allow it to populate from there.

TO be honest im a bit lost with this one and think rather than describng one element of the issue i may be best learnign how to do a youtube of my screen and showing you the total problem so can get the best advice to cover all areas.

But thanks for the above advice it has given me some ideas to play with.

 

[static]

I don't know if 2) was referring to my example or not (I didn't use any text boxes).
But if so, the value (text) displayed when you click an option would be a numeric ID in a real-world scenario.

You could easily adapt it to show checkboxes beside items for multiple selections.

You only need to provide an adequate description of the problem and I'm sure somebody here could help.

Databases seem really complicated but they are actually very simple.
You have tables that store data.
Data can take up a lot of space, so you try to remove as much duplication as possible.
Numbers take up less space than text.
So, if you use a text value a lot, you replace it with a number, or ID and use the ID instead of the text. That's it in a nutshell.

How data is presented to the user is personal preference.
There is no right or wrong way.

 

[Mark_]

ChrisJames25,

Please look at this sample I'm attaching. I use a set of queries to form the columns then another query to merge. I am currently manually assigning row/column for this sample.

On double click it pulls up an update form that allows for each record to be individually updated. This may be a more user friendly way to do what you wish, especially as they need to press save or undo for that record.

Let me know if this is much closer to what you were looking for.

 

[chrisjames25]

Hi Mark

Thanks fortaking the time to have a play with my problem.

REally like what you have done at a quick glance. THink it is great.

When i get to work i will have a look at the code behind it and no doubt come back with some questions.

CHeers

 

[chrisjames25]

Hi Mark

Quick follow up on your code regarding the column number and how i can program the nuimber in VBA.

To do this i am thinking i need to do the following when open a form:

1. Run a query that filters variety by a filter based on category that can be hard coded as it will be associated with that command button
2. Run a Dcount on the query to find out how many records are within the query. (for this exmaple lets say 93)

I'm ok to this point.

3. Run an amend query to label the first 20 records with the number 1 (assigning the column) In my head im thinking i could do this with a loop setting the limits form 1-20. ANd if i have a column in the query in 1. above then i could have a column called column name and update it with the loop.

4. I could make the code in vba look for the first null value in the query and then update the next 20 records. Once that loop is done go to next loop that looks for next null record in that column and loop next 20 adding 2 to column etc etc.

5. Not sure if it would bug when i got to fifth loop and there wasnt a multiple of 20. THere was only 13 leftover in my example.

Guess a work around in this case would be at beginning to define QryCount as integer and do Qrycount = 93-20 and then beofre each loop do if statement to check number greater than 20 and when it isnt do n = 1 - QryCount.

THere is prob a lot simpler way of doing what i have stated above but these are my thinking so interested whether it is anywhere near the mark and if not any suggestions would be great.

Cheers

 

[Mark_]

You could try something like the following...

Dim asSQL as String
Dim rs as DAO.RecordSet
Dim aiRow as Integer
Dim aiCol as Integer
Dim aiRowMax as Integer

  asSQL = What ever you need to find the records to update

  'We are using DCount to find out how many record.
  'We are then dividing the result to get a negative value
  'since the INT function will round UP negative values.
  'Then multiply by -1 to get a positive.
  aiRowMax = Int( DCount("[Field]","[Table]","Where clause for asSQL")/-5) * -1

  Set RS = CurrentDB.OpenRecordset (asSQL)
  aiRow = 1 'Start with row 1, col 1.
  aiCol = 1
  
   If Not (rs.EOF And rs.BOF) Then
       rs.MoveFirst 'Unnecessary in this case, but still a good habit
       Do Until rs.EOF = True
          rs.Edit 'Edit the current record to fill in row and column
          rs!Row = aiRow
          rs!Col = aiCol
          rs.Update 'Update the current record.
          aiRow = aiRow + 1 'Increment the row counter.

          if aiRow > aiRowMax then 'IF we have passed the max row value,
             aiCol = aiCol + 1 'go to the next column and start over.
             aiRow = 1
         End IF
          'Move to the next record. Don't ever forget to do this.
          rs.MoveNext
       Loop
   Else
       MsgBox "There are no records in the recordset."
   End If

I haven't tried the code but it should point you in the right direction.

 

[chrisjames25]

Hi MArk

Cheers for tis code. Manic at work today so no time to test but that code looks great and best of all makes sense to me which is a real plus.

I'll give it a go and let you know how it works out.

Cheers

Chris